Back to the drawing board. Create table as creates the columns with types based on their declared affinity - I particularly need to identify columns in the new table that existed as blobs in the original tables, create table as gives these an affinity of "", I really need the original type definition :(
On 16 September 2014 18:18, Paul Sanderson <sandersonforens...@gmail.com> wrote: > 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 > > -- 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
