The attached patch implements the compound (multi-row) INSERT statement syntax against the SQLite 3.5.1 source tree. Both named and unnamed INSERT column syntax with DEFAULT column values are supported.
The patch transforms multi-row INSERT statements into compound SELECT statements separated by UNION ALLs. It should be fairly efficient, as all rows within the compound INSERT are inserted within a single implicit transaction. SQLITE_MAX_COMPOUND_SELECT in sqliteLimit.h also serves as the maximum number of rows in a multi-row INSERT. No regressions in "make test". Post any problems to the mailing list, and I'll try to keep this patch up to date. This patch is hereby placed in the public domain. Examples: SQLite version 3.5.1 Enter ".help" for instructions sqlite> CREATE TABLE foo(a DEFAULT 123, b DEFAULT 'Bob'); sqlite> insert into foo values (1,'Ace'), (2,'Two'), (3,'Three'); sqlite> select * from foo; 1|Ace 2|Two 3|Three sqlite> delete from foo; sqlite> insert into foo(b,a) values (1,'Ace'), (2,'Two'), (3,'Three'); sqlite> select * from foo; Ace|1 Two|2 Three|3 sqlite> delete from foo; sqlite> insert into foo(a) values (10), (20), (30); sqlite> select * from foo; 10|Bob 20|Bob 30|Bob ____________________________________________________________________________________ Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow
Index: src/parse.y =================================================================== RCS file: /sqlite/sqlite/src/parse.y,v retrieving revision 1.234 diff -u -3 -p -r1.234 parse.y --- src/parse.y 21 Aug 2007 10:44:16 -0000 1.234 +++ src/parse.y 14 Oct 2007 07:29:32 -0000 @@ -624,6 +624,42 @@ inscollist(A) ::= inscollist(X) COMMA nm inscollist(A) ::= nm(Y). {A = sqlite3IdListAppend(pParse->db,0,&Y);} +%ifndef SQLITE_OMIT_COMPOUND_INSERT +%ifndef SQLITE_OMIT_COMPOUND_SELECT + +%type inslistItem {Select*} +%destructor inslistItem {sqlite3SelectDelete($$);} + +inslistItem(A) ::= LP itemlist(W) RP. { + SrcList *pSrc = sqlite3DbMallocZero(pParse->db, sizeof(SrcList)); + A = sqlite3SelectNew(pParse,W,pSrc,0,0,0,0,0,0,0); +} + +%type inslist {Select*} +%destructor inslist {sqlite3SelectDelete($$);} + +%type comma_opt {int} +comma_opt(X) ::= . {X = 0;} +comma_opt(X) ::= COMMA. {X = 1;} + +inslist(A) ::= inslistItem(Z) COMMA. {A = Z;} +inslist(A) ::= inslist(X) inslistItem(Z) comma_opt(C). { + C = C; + if( Z ){ + Z->op = TK_ALL; + Z->pPrior = X; + }else{ + sqlite3SelectDelete(X); + } + A = Z; +} + +cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) VALUES inslist(S). + {sqlite3Insert(pParse, X, 0, S, F, R);} + +%endif SQLITE_OMIT_COMPOUND_SELECT +%endif SQLITE_OMIT_COMPOUND_INSERT + /////////////////////////// Expression Processing ///////////////////////////// // Index: src/sqliteLimit.h =================================================================== RCS file: /sqlite/sqlite/src/sqliteLimit.h,v retrieving revision 1.2 diff -u -3 -p -r1.2 sqliteLimit.h --- src/sqliteLimit.h 24 Aug 2007 11:52:29 -0000 1.2 +++ src/sqliteLimit.h 14 Oct 2007 07:29:33 -0000 @@ -75,7 +75,7 @@ ** any limit on the number of terms in a compount SELECT. */ #ifndef SQLITE_MAX_COMPOUND_SELECT -# define SQLITE_MAX_COMPOUND_SELECT 500 +# define SQLITE_MAX_COMPOUND_SELECT 5000000 #endif /*
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------