How do I Populate a 2-Column table with Unrelated Data from 2 Different Sources?

Question

I have 2 tables, each with an identity column. What I want to do is populate a new 2-column table with those identities so that it results in a pairing of the identities.

Now, I am perfectly able to populate one column of my new table with the identities from one of the tables, but can't get the identities from the other table into my new table. If this isn't the best 1st step to take though, please let me know.

Thank you

Answer 1

You can populate a table with the INSERT...SELECT syntax, and the SELECT can be the result of a join between two (or more) tables.

INSERT INTO NewTable (col1, col2)
  SELECT a.col1, b.col2
  FROM a JOIN b ON ...conditions...;

So if you can express the pairing as a SELECT, you can insert it into your table.

If the two tables are unrelated and there's no way to express the pairing, then you're asking how to make a non-relational data store, and there are no relational rules for that.

Answer 2

You may want to try something like the following:

INSERT INTO t3 (id, value_1, value_2)
SELECT t1.id, t1.value, t2.value 
FROM   t1 
JOIN   t2 ON (t2.id = t1.id);

Test case (MySQL):

CREATE TABLE t1 (id int, value int);
CREATE TABLE t2 (id int, value int);
CREATE TABLE t3 (id int, value_1 int, value_2 int);

INSERT INTO t1 VALUES (1, 100);
INSERT INTO t1 VALUES (2, 200);
INSERT INTO t1 VALUES (3, 300);

INSERT INTO t2 VALUES (1, 10);
INSERT INTO t2 VALUES (2, 20);
INSERT INTO t2 VALUES (3, 30);

Result:

SELECT * FROM t3;
+------+---------+---------+
| id   | value_1 | value_2 |
+------+---------+---------+
|    1 |     100 |      10 |
|    2 |     200 |      20 |
|    3 |     300 |      30 |
+------+---------+---------+
3 rows in set (0.00 sec)

Answer 3

An option would be to create a counter for each of the columns that would operate as a unique identifier and then join on the counter.

For SQL Server this would work:

SELECT one.column1, two.column2
FROM (SELECT RANK() OVER (ORDER BY column1) AS id, 
             column1 
      FROM table1) one
LEFT JOIN (SELECT RANK() OVER (ORDER BY column2) AS id, 
                  column2 
           FROM table2) two ON one.id = two.id