Thanks Ryan. That doesn't work for me though as I am looking for a generic solution that will work on multiple tables - so no hard coding of column definitions :(
I think I am getting there On 16 September 2014 15:38, RSmith <rsm...@rsweb.co.za> wrote: > > 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 > -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - Deleted SQLite recovery http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
