OK, thanks for the reply. I am using the same construction to write directly from Interbase to Access and that works fine. I can make an ADO recordset first from the Interbase data and write that to SQLite in a (double) loop, but it is a bit slow. Maybe I should write to text first (which is quite fast) and then write to SQLite. I am not sure though how to import text into SQLite. Trouble with the SQLite ODBC driver is that documentation is a bit sparse. Maybe I should use a transaction, but not sure how that works either.
RBS -----Original Message----- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 14 November 2006 03:24 To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT INTO with SELECT On 11/13/06, RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to move data from Interbase to SQLite via the ODBC driver and ADO and > having trouble to get the right syntax for the INSERT INTO statement. > > This is what I have now, but it fails with the error: only one SQL statement > allowed. I notice your code creates this sql: INSERT INTO READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60) SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60 FROM READCODE R IN " [ ? ] " Which isn't valid. The IN clause is part of WHERE, not a separate qualifier. It works like this: select x as prime from mytable where x in ( 1,2,3,5,7 ); Since the select is reading from READCODE you're inserting values into the same table you're selecting them from. There's no way that I know of to use odbc to copy from one database to another unless they're both of the same type. I would do it by selecting all the values to copy in one statement and writing them to memory or a disk file. Then import to the other database in a second step. -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
