On 3/7/2015 1:42 PM, R.Smith wrote:
>
> Hi Dave, you did not give us the schemata so I'm going to guess you
> have tables like this:
>
> CEATE TABLE T1("ID" INT PRIMARY KEY, "val1" TEXT);
> CEATE TABLE T2("ID" INT PRIMARY KEY, "val2" TEXT);
> CEATE TABLE T3("ID" INT PRIMARY KEY, "val3" TEXT);
> ... all the way to ...
> CEATE TABLE T7("ID" INT PRIMARY KEY, "val7" TEXT);
>
> And now you figured out best would be to have 1 Table like this"
>
> CEATE TABLE T_ALL("ID" INT PRIMARY KEY, "val1" TEXT, "val2" TEXT,
> "val3" TEXT, "val4" TEXT, "val5" TEXT, "val6" TEXT, "val7" TEXT);
>
> Right?
>
> If this is the case, assuming[1] all ID's are present in all tables,
> the query to put all the single tables into the big one is simply this:
>
> INSERT INTO T_ALL (ID, val1, val2, val3, val4, val5, val6, val7)
> SELECT T1.ID, T1.val1, T2.val2, T3.val3, T4.val4, T5.val5, T6.val6,
> T7.val7
> FROM T1
> LEFT JOIN T2 ON T2.ID=T1.ID
> LEFT JOIN T3 ON T3.ID=T1.ID
> LEFT JOIN T4 ON T4.ID=T1.ID
> LEFT JOIN T5 ON T5.ID=T1.ID
> LEFT JOIN T6 ON T6.ID=T1.ID
> LEFT JOIN T7 ON T7.ID=T1.ID
> WHERE 1;
>
> This simply looks up the same ID in all the tables and inserts each
> tables value for the val column into the main table. You will have to
> (obviously) substitute your actual table/column names.
>
> [1] : If all the IDs are not present in all the columns, you will get
> NULL values inserted in the main table or completely miss out some IDs
> (if not present in T1), so if this is the case, let us know the exact
> schema and layout to figure out a more correct transfer.
>
> HTH
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
Ryan,
After reviewing your answer some more you pretty much nailed it. I
was trying some cut/paste method earlier with no luck at all and getting
the field filled with nulls and the data appended to the row(2s) instead
of the columns. Thanks again for your help. I have printed this out for
future reference. :-)
Dave
