SQL: Single select query from 2 Non-Joining tables

I have 2 tables which doesn't have any references to each other and I'm trying to开发者_JAVA百科 create a third table (for reference lookup) by selecting from fields from both tables.

TableA has an A_ID
TableB has a B_ID

I want to create Table C which has a 1 to 1 reference between A_ID to B_ID where A_ID = FirstID and B_ID = SecondID, I can't join the 2 tables because there's nothing in common.

Something like:

Insert INTO [TableC]
(FirstID, SecondID)
SELECT
A_ID As FirstID,
(Select B_ID From TableB)
FROM TableA

Basically we are creating a relationship right now with Table C so that we can use it to reference the two tables in the future using the their IDs.

Assuming TableA and TableB truly have nothing in common, then what you want is the Cartesian product, that is, for every row in A times every row in B.

INSERT INTO TableC(FirstID,SecondID)
SELECT A_ID,B_ID
FROM TableA
CROSS JOIN TableB

Perhaps what you really want is to join them by ROW_NUMBER().

INSERT INTO TableC(FirstID,SecondID)
SELECT A_ID,B_ID
FROM (SELECT A_ID,ROW_NUMBER() OVER (ORDER BY whatever) as rownumA FROM TableA) a
FULL OUTER JOIN (SELECT B_ID,ROW_NUMBER() OVER (ORDER BY whatever) as rownumB FROM TableB) b ON a.rownumA=b.rownumB