You will also note that doing something like: create [temporary] table x as select a, b, ... from y ...
will result in calculated columns being inserted with affinity None. If you need a specific affinity you need to cast the expression result to that type affinity. Also, if it matters, text columns will not carry over the collation to the new table definition. If you need the columns to have a specific collation, or columns containing computed results to have a specific affinity (and you do not want to always have to cast(...) either the source or destination, then you need to create the destination table first, then insert into ... as select ...; >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of RSmith >Sent: Tuesday, 16 September, 2014 08:38 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Create join and add unique column > > >On 2014/09/16 15:32, Paul Sanderson wrote: >> select _rowid_, * from tab3 does the trick - thanks all > >Indeed, and if you are pedantic or do not work in a table with rowids, >the solution is to explicitly give the table definition then >fill it, some variation on this: > >CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, >name TEXT, country TEXT); >INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, >tab2 C; > >tab 3 should now look like this (according to your sample tables): >rowNo| name | country >----------------------------------------- > 1 | paul | uk > 2 | paul | scotland > 3 | helen | uk > 4 | helen | scotland > 5 | melanie | uk > 6 | melanie | scotland > > >NOTE: >In-case you are not familiar with it - That insert omits the rowNo and >can be thought of as a variation of this query which achieves >the same: >INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country >FROM tab1 N, tab2 C; > > >Hope that widens your SQL arsenal another micron, Cheers! >Ryan > > >_______________________________________________ >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
