RE: [sqlite] Dump with where clause
But then I have to create an actual table in the database? I suppose I can do the following: BEGIN; create table image_temp as select * from file_folder_data; .dump image_temp Rollback; That seems to work as expected Just another question though, how can I script this and run it from command line, using sqlite3.exe ? Thanks. -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: 17 August 2007 01:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dump with where clause Updating Igor's suggestion, just remove 'temp' from the line create temp table image_temp as select * from file_folder_data; For me .dump then works as expected. Rgds, Simon On 17/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > Hi Dennis this seems like a good idea, but there is a problem: > > I use the following statement: > > .mode insert > select * from FILE_FOLDER_DATA; > > > This is a snippet of what I get > INSERT INTO table VALUES(1285,4323,2,'╪α','Thumb','JPEG'); > INSERT INTO table VALUES(1286,4324,2,'╪α','Thumb','JPEG'); > INSERT INTO table VALUES(1287,4325,2,'╪α','Thumb','JPEG'); > INSERT INTO table VALUES(1288,4326,2,'╪α','Thumb','JPEG'); > > As you can see first of all the insert statement inserts into 'table' which > is obviously not the right name, > > Secondly the image data should be a HEX string as is what .dump does. > > .schema file_folder_data > > CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, > FOLDER_ID I > NTEGER, FOLDER_TYPE INTEGER, > CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT); > CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE > ASC > ); > > > Also the .dump with temp table as Igor suggested does not work. > > This works: > .dump file_folder_data > > This does NOT > create temp table image_temp as select * from file_folder_data; > .dump image_temp > > All I get is: > > BEGIN TRANSACTION; > COMMIT; > > > > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: 16 August 2007 05:49 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Dump with where clause > > Andre du Plessis wrote: > > HI, how can I use .dump or something similar but specify a where clause, > > I cant see that the .dump command allows this, > > > > Without any arguments it seems to dump the whole db, the only argument > > supported is the table name, > > > > > > > > I would like to be able to do something like: > > > > .dump table1 where ID > 1000 > > > > > > > > I don't have a problem with the INSERT into statements, in fact I think > > I prefer it because the main idea is to extract parts of the db > > (revisions), > > > > And then to be able to rebuild the db in case of corruption... > > > > > > > > I know there is also the COPY command in SQL I have not really tried it > > by the documentation it seems to be able to dump the table in comma or > > tab delimited, but Preferably I don't want to write too much code to do > > this. > > > > > > > > > Andre, > > You can use the insert mode in the shell to do what you want. It will > format the select output as insert statements. > >.mode insert >select * from table1 where ID > 1000; > > This doesn't generate the transaction wrapper, or the table's create > statement, but you can add those yourself if needed. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > >
Re: [sqlite] Dump with where clause
Updating Igor's suggestion, just remove 'temp' from the line create temp table image_temp as select * from file_folder_data; For me .dump then works as expected. Rgds, Simon On 17/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > Hi Dennis this seems like a good idea, but there is a problem: > > I use the following statement: > > .mode insert > select * from FILE_FOLDER_DATA; > > > This is a snippet of what I get > INSERT INTO table VALUES(1285,4323,2,'�nα','Thumb','JPEG'); > INSERT INTO table VALUES(1286,4324,2,'�nα','Thumb','JPEG'); > INSERT INTO table VALUES(1287,4325,2,'�nα','Thumb','JPEG'); > INSERT INTO table VALUES(1288,4326,2,'�nα','Thumb','JPEG'); > > As you can see first of all the insert statement inserts into 'table' which > is obviously not the right name, > > Secondly the image data should be a HEX string as is what .dump does. > > .schema file_folder_data > > CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, > FOLDER_ID I > NTEGER, FOLDER_TYPE INTEGER, > CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT); > CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE > ASC > ); > > > Also the .dump with temp table as Igor suggested does not work. > > This works: > .dump file_folder_data > > This does NOT > create temp table image_temp as select * from file_folder_data; > .dump image_temp > > All I get is: > > BEGIN TRANSACTION; > COMMIT; > > > > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: 16 August 2007 05:49 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Dump with where clause > > Andre du Plessis wrote: > > HI, how can I use .dump or something similar but specify a where clause, > > I cant see that the .dump command allows this, > > > > Without any arguments it seems to dump the whole db, the only argument > > supported is the table name, > > > > > > > > I would like to be able to do something like: > > > > .dump table1 where ID > 1000 > > > > > > > > I don't have a problem with the INSERT into statements, in fact I think > > I prefer it because the main idea is to extract parts of the db > > (revisions), > > > > And then to be able to rebuild the db in case of corruption... > > > > > > > > I know there is also the COPY command in SQL I have not really tried it > > by the documentation it seems to be able to dump the table in comma or > > tab delimited, but Preferably I don't want to write too much code to do > > this. > > > > > > > > > Andre, > > You can use the insert mode in the shell to do what you want. It will > format the select output as insert statements. > >.mode insert >select * from table1 where ID > 1000; > > This doesn't generate the transaction wrapper, or the table's create > statement, but you can add those yourself if needed. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > >
RE: [sqlite] Dump with where clause
Hi Dennis this seems like a good idea, but there is a problem: I use the following statement: .mode insert select * from FILE_FOLDER_DATA; This is a snippet of what I get INSERT INTO table VALUES(1285,4323,2,' ╪ α','Thumb','JPEG'); INSERT INTO table VALUES(1286,4324,2,' ╪ α','Thumb','JPEG'); INSERT INTO table VALUES(1287,4325,2,' ╪ α','Thumb','JPEG'); INSERT INTO table VALUES(1288,4326,2,' ╪ α','Thumb','JPEG'); As you can see first of all the insert statement inserts into 'table' which is obviously not the right name, Secondly the image data should be a HEX string as is what .dump does. .schema file_folder_data CREATE TABLE FILE_FOLDER_DATA (ID INTEGER PRIMARY KEY AUTOINCREMENT, FOLDER_ID I NTEGER, FOLDER_TYPE INTEGER, CONTENT BLOB, CONTENT_NAME TEXT, CONTENT_EXT TEXT); CREATE INDEX IDX_FILE_FOLDER_DATA ON FILE_FOLDER_DATA(FOLDER_ID, FOLDER_TYPE ASC ); Also the .dump with temp table as Igor suggested does not work. This works: .dump file_folder_data This does NOT create temp table image_temp as select * from file_folder_data; .dump image_temp All I get is: BEGIN TRANSACTION; COMMIT; -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 16 August 2007 05:49 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dump with where clause Andre du Plessis wrote: > HI, how can I use .dump or something similar but specify a where clause, > I cant see that the .dump command allows this, > > Without any arguments it seems to dump the whole db, the only argument > supported is the table name, > > > > I would like to be able to do something like: > > .dump table1 where ID > 1000 > > > > I don't have a problem with the INSERT into statements, in fact I think > I prefer it because the main idea is to extract parts of the db > (revisions), > > And then to be able to rebuild the db in case of corruption... > > > > I know there is also the COPY command in SQL I have not really tried it > by the documentation it seems to be able to dump the table in comma or > tab delimited, but Preferably I don't want to write too much code to do > this. > > > > Andre, You can use the insert mode in the shell to do what you want. It will format the select output as insert statements. .mode insert select * from table1 where ID > 1000; This doesn't generate the transaction wrapper, or the table's create statement, but you can add those yourself if needed. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dump with where clause
Andre du Plessis wrote: HI, how can I use .dump or something similar but specify a where clause, I cant see that the .dump command allows this, Without any arguments it seems to dump the whole db, the only argument supported is the table name, I would like to be able to do something like: .dump table1 where ID > 1000 I don't have a problem with the INSERT into statements, in fact I think I prefer it because the main idea is to extract parts of the db (revisions), And then to be able to rebuild the db in case of corruption... I know there is also the COPY command in SQL I have not really tried it by the documentation it seems to be able to dump the table in comma or tab delimited, but Preferably I don't want to write too much code to do this. Andre, You can use the insert mode in the shell to do what you want. It will format the select output as insert statements. .mode insert select * from table1 where ID > 1000; This doesn't generate the transaction wrapper, or the table's create statement, but you can add those yourself if needed. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Dump with where clause
HI, how can I use .dump or something similar but specify a where clause, I cant see that the .dump command allows this, Without any arguments it seems to dump the whole db, the only argument supported is the table name, I would like to be able to do something like: .dump table1 where ID > 1000 I don't have a problem with the INSERT into statements, in fact I think I prefer it because the main idea is to extract parts of the db (revisions), And then to be able to rebuild the db in case of corruption... I know there is also the COPY command in SQL I have not really tried it by the documentation it seems to be able to dump the table in comma or tab delimited, but Preferably I don't want to write too much code to do this. Thanks.
