Re: [sqlite] about insert into select
On May 21, 2009, at 8:43 AM, Wenton Thomas wrote: > What's the execution sequence about " insert ino A select > from B "? > I means,which is correct prescription in the following: > (1) select all rows from B at first, then insert all the result > into table A; > (2) select a row from B ,then insert the row into table A > immediately, repeat the precess. Usually strategy 2. Falls back to 1 if A and B are the same table, or if there are triggers that imply inserting rows into B may modify A. Dan. > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about insert into select
On 21/05/2009 11:43 AM, Wenton Thomas wrote: > What's the execution sequence about " insert ino A select from B "? > I means,which is correct prescription in the following: > (1) select all rows from B at first, meanwhile this product (frequently chosen for use on devices with minimal memory and slow pseudo-disk storage) would be buffering up the selected rows where? > then insert all the result into table A; > (2) select a row from B ,then insert the row into table A immediately, repeat > the precess. So the least implausible possibility is ... ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about insert into select
Wenton Thomas wrote: > What's the execution sequence about " insert ino A select from B "? > I means,which is correct prescription in the following: > (1) select all rows from B at first, then insert all the result into > table A; > (2) select a row from B ,then insert the row into table A immediately, repeat > the precess. > > When in doubt ask sqlite. :-) SQLite version 3.6.14 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .explain on sqlite> create table t1(a,b); sqlite> create table t2(c,d); sqlite> explain insert into t2 select * from t1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 13000 2 Noop 0 0 000 3 OpenWrite 1 3 0 2 00 4 OpenRead 0 2 0 2 00 5 Rewind 0 10000 6 NewRowid 1 2 000 7 RowData0 1 000 8 Insert 1 1 2 t2 0b 9 Next 0 6 000 10Close 0 0 000 11Close 1 0 000 12Halt 0 0 000 13Transaction0 1 000 14VerifyCookie 0 2 000 15TableLock 0 3 1 t2 00 16TableLock 0 2 0 t1 00 17Goto 0 2 000 sqlite> From this it's easy (relatively) to see that inserts each row as it find them while executing the select. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about insert into select
What's the execution sequence about " insert ino A select from B "? I means,which is correct prescription in the following: (1) select all rows from B at first, then insert all the result into table A; (2) select a row from B ,then insert the row into table A immediately, repeat the precess. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
