TSQL - Merge two tables

Question

I have a following task: I have two single column tables in a procedure and both of them have the same amount of rows. I'd like to "merge" them so I get a resulting table with 2 columns. I there some easy way for this?

In worst case I could try to add primary key and use INSERT INTO ... SELECT with JOIN but it requires quite big changes in the code I already have so I decided to ask you guys.

Just to explain my answer below, here's the example. I have following tables:

tableA
col1
----
1
2
3
4

tableB
col2
----
a
b
c
d

Resulting table:
col1 | col2
1    | a
2    | b
3    | c
4    | d

Answer 1

You can do this:

SELECT t1.col1, t2.col1 AS col2
INTO NewTable
FROM
(
   SELECT col1, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RN
   FROM table1
) AS t1
INNER JOIN
(
   SELECT col1, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RN
   FROM table2
) AS t2 ON t1.rn = t2.rn

This will create a brand new table NewTable with the two columns from the two tables:

| COL1 | COL2 |
---------------
|    1 |    a |
|    2 |    b |
|    3 |    c |
|    4 |    d |

See it in action here:

• SQL Fiddle Demo.