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] > ----------------------------------------------------------------------------- > >
