On 20 Mar 2019, at 00:35, Simon Davies <simon.james.dav...@gmail.com> wrote:
> On Tue, 19 Mar 2019 at 15:07, Tim Streater <t...@clothears.org.uk> wrote: >> >> My use case is a mixture of these. My need is to copy a row from a table in >> one db (db1) to a table with identical schema in another db (db2). The >> complication is that there is an id column, so the row needs to get a new id >> in db2. >> >> At the minute I open db1, and do these steps (absid is the id column): >> >> attach database ':memory:' as mem >> create table mem.messages as select * from main.messages where >> absid=<some_value> >> update mem.messages set absid=null >> attach database 'db2' as dst >> insert into dst.messages select * from mem.messages >> >> which works nicely but has too many steps. I've not found a way to reduce >> the step count. > > absid is integer primary key, or a new id would not be generated in > the above scenario; so the following should work: > > insert into dst.messages( notabsid_1, notabsid2,...) select > notabsid_1, notabsid_2,... from main.messages; Yes, I thought about that at the time, but preferred what I have done, as this allows me to have just the one place in the app where the table schema is defined. I do this trick to move or copy a row in several places in the app, with slight variants, and explicitly spelling out all the columns would mean I'd have to be sure I'd updated that in a number of places, were the schema to change from time to time. Not ideal, but better from a maintenance PoV. -- Cheers -- Tim _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
