On Sun, 8 Sep 2013 22:56:20 +0000, "Joseph L. Casale" <jcas...@activenetwerx.com> wrote:
>Hi, >What is the most efficient way to insert several records into a table which >has a fk ref to the auto incrementing pk of another insert I need to do in the >same statement. What is efficient? Apparently you are not looking for performance, but for short SQL code. In that case I think a combination of updateble view (instead of triggers are very powerful) and emulated variables might be what your are looking for. Have a look at this example: http://ideone.com/C36YV Rewrite for your use case... I agree with Keith Medcalf that the "val" in your case would have to be declared unique, and inserts into a should be insert or ignore. This is partially demonzstrated in http://ideone.com/bTOre . In the latter, the "a" tables are presumed preloaded in a separate pass, you could try to combine the triggers in both examples to take care of that. >I am migrating some code away from using the SQLAlchemy orm to using the >Core. The way the data is returned to me is a string (requiring an insert into >table A) >accompanied by several more strings (requiring inserts into table B with a ref >to a pk >in table A's row). > >So instead of doing this the typical way, if I can prepare all the sql as one >large >statement for several sets of related inserts (The initial insert into table A >with all >the related inserts into table B) I will get the performance I am after. > >Does this seem reasonable? Sqlite doesn't support variable declaration but I am >sure there is a more efficient means to this using something along the lines of >INSERT INTO SELECT, just not sure how to craft this with "n" inserts based on >one >select from the PK generating initial insert. > >Thanks, >jlc HTH -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
