On 10 Jul 2014 at 12:45, Simon Slavin <slav...@bigfraud.org> wrote: > On 10 Jul 2014, at 11:47am, Clemens Ladisch <clem...@ladisch.de> wrote: > >> This is indeed misleading. The result set actually has columns and >> column names even when there are now rows in it, so this is guaranteed >> to work even for empty result sets.
Thanks. I'll work along those lines then. > However, all is not as you might expect: [snip] > Note that although affinities are preserved when doing things this way, you > lose COLLATE and constraint information, and that you can get some strange > results if your "SELECT" is anything except "SELECT *". In other words, this > is good for preserving values, but not good for preserving other elements of a > table definition. Righto - I'll keep it simple :-) What I'm actually doing is moving/copying a row from one database to another, where the two databases have identical schemas. At present the sequence will be something like (absid is integer primary key): 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 'dest_db' as dst; insert into dst.messages select * from mem.messages; <do something here to get the last insert id in dst.messages> So there are two copy steps. What I'd like to do is: attach database 'dest_db' as dst; insert into dst.messages select * from main.messages; <do something here to get the last insert id in dst.messages> but unfortunately there could easily be a conflict in the absid values, so I have to be able to set it to null to get a new value generated. -- Cheers -- Tim
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
