CODEWITHSUNDEEP
sqldb2insert-into
joshi123
joshi123

Reputation: 865

db2 INSERT INTO target table from multiple source tables

I have two source tables, TABLE_1 and TABLE_2

They each have one column of data I'd like to grab, and each have the exact same number of rows and I'd like to match them up in a third table pair-wise like so:

Column_A | Column_B
    Val1 | ValA
    Val2 | ValB
    Val3 | ValC
    Val4 | ValD

When using this syntax the data in Table_3 doesn't match up, the rows in Column_B are being populated with missing fields instead of lining up next to the Column_A values. 

INSERT INTO Table_3 (Column_A)
SELECT Column_A FROM Table_1;
INSERT INTO Table_3 (Column_B)
SELECT Column_A FROM Table_2;

Upvotes: 0

Views: 2014

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

First, just because you have the same rows doesn't mean that you can readily combine them. You can join them together:

INSERT INTO Table_3 (Column_A, Column_B)
    SELECT t1.Column_A, t2.Column_B
    FROM Table_1 t1 JOIN
         Table_2 t2
         ON t1.?? = t2.??

Your question does not clarify what the JOIN keys should be.

If you don't have JOIN keys but want arbitrary pairs, you can do use row_number() to assign a join key:

INSERT INTO Table_3 (Column_A, Column_B)
    SELECT t1.Column_A, t2.Column_B
    FROM (SELECT t1.*, ROW_NUMBER() OVER (ORDER BY Column_A) as seqnum Table_1 t1
         ) JOIN
         (SELECT t2.*, ROW_NUMBER() OVER (ORDER BY Column_B) as seqnum Table_2 t2
         ) t2
         ON t1.seqnum = t2.seqnum;

Upvotes: 2

Related Questions