If you are using a programming language, simply retrieve the id after the first
insert, then bind that host variable when executing subsequent statements.
You can also try something like:
BEGIN IMMEDIATE;
Insert into table1 values ('...');
Insert into table2 (id, key, val)
Select id, key, value
from (select 'key1' key, 'val1' val
Union
Select 'key2', 'val2'
... and as many union and selects as you want) as A,
(select (select last_insert_id()) id) as B;
COMMIT;
if your programming environment does not support function calls and bound
values.
Of course, table_a.val should probably be unique, in which case you can simply
select the id corresponding to that val in subsequent inserts.
> -----Original Message-----
> From: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of Joseph L. Casale
> Sent: Sunday, 8 September, 2013 20:17
> To: [email protected]
> Subject: Re: [sqlite] Insert statement
>
> > If I understand the question, and there is no key other than the auto-
> incrementing
> > integer, there might not be a good way. It sounds like the database's
> design may
> > have painted you into a corner.
>
> Hi James,
> Well, after inserting one row into table A which looks like (without
> specifying the id
> and letting it auto generate):
>
> CREATE TABLE table_a (
> val VARCHAR COLLATE "nocase" NOT NULL,
> id INTEGER NOT NULL,
> PRIMARY KEY ( id )
> );
>
> (forgive that odd looking format, its SQLAlchemy output...)
>
> I have for example 20 rows in table B to insert referencing the above:
>
> CREATE TABLE table_b (
> val VARCHAR COLLATE "nocase",
> key VARCHAR COLLATE "nocase" NOT NULL,
> id INTEGER,
> seqno INTEGER NOT NULL,
> PRIMARY KEY ( seqno ),
> FOREIGN KEY ( id ) REFERENCES table_a ( id )
> );
>
> So selecting last_insert_rowid() always gives me the 'id' of the
> previous row from table_a
> after an insert. So I would insert into table_a, get that rowid, and
> build the remaining 20
> inserts. For the sake of keeping the entire sql statement manageable, I
> was hoping not to
> build the next 20 statements based on SELECT id FROM table_a WHERE
> val='xxx' as that string
> will be very long.
>
> So this works for one insert:
>
> INSERT INTO table_a (val) VALUES ('xxxxxx');
> INSERT INTO table_b (id, key, val)
> SELECT last_insert_rowid(), 'yyy', 'zzz';
>
> Just not sure how to perform 20 or 30 of those inserts into table_b
> after the one into table_a
> yields the id value I need.
>
> Thanks!
> jlc
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
