sql to combine two unrelated tables into one

I have tables

table1

col1    col2    
a       b
c       d

and table2

mycol1  mycol2
e           f
g           h
i           j
k           l

I want to combine the two tables, which have no common field into one table looking like:

table 3

col1    col2    mycol1  mycol2
a           b   e   f
c           d   g   h
null    null    i   j
null    null    k   l

ie, it is like putting the two tables side by side.

I’m stuck! Please help!

Answer

Get a row number for each row in each table, then do a full join using those row numbers:

WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY col1) AS ROWNUM, * FROM Table1
),
CTE2 AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY mycol1) AS ROWNUM, * FROM Table2
)
SELECT col1, col2, mycol1, mycol2
FROM CTE1 FULL JOIN CTE2 ON CTE1.ROWNUM = CTE2.ROWNUM

This is assuming SQL Server >= 2005.