Create a rowID / identity column in a cross-join subquery

Question

I have a table with some of the combinations of two ids. I need to create a unique id for each other combination. Since some combinations already exist, I can't just use an identifier column.

The following code works:

CREATE TABLE #A (A_ID INT)
CREATE TABLE #B (B_ID INT)
CREATE TABLE #C (C_ID INT, A_ID INT, B_ID INT)
CREATE TABLE #D (C_ID INT, A_ID INT, B_ID INT)
INSERT INTO #A VALUES (1),(2)
INSERT INTO #B VALUES (1),(2)
INSERT INTO #C VALUES (1,1,1),(2,1,2)
SELECT IDENTITY(INT,1,1) AS N, a.A_ID, b.B_ID
INTO #temp
FROM #A a 
CROSS JOIN #B b
INSERT INTO #D (C_ID, A_ID, B_ID)
 SELECT ISNULL(c.C_ID, -1 * temp.N), temp.A_ID, temp.B_ID
 FROM #temp temp
 LEFT OUTER JOIN #C c ON c.A_ID = temp.A_ID 
 AND c.B_ID = temp.B_ID
SELECT * FROM #D

Result:

C_ID A_ID B_ID
--------------
1 1 1
-2 2 1
2 1 2
-4 2 2

However, this is kinda ugly and I feel like I should be able to do this as a subquery, something like:

INSERT INTO #D (C_ID, A_ID, B_ID)
 SELECT ISNULL(c.C_ID, -1 * temp.N), temp.A_ID, temp.B_ID
 FROM
 (SELECT 
 IDENTITY(INT, 1, 1) AS N, 
 a.A_ID, b.B_ID
 FROM 
 #A a 
 CROSS JOIN 
 #B b) temp
 LEFT OUTER JOIN 
 #C c ON c.A_ID = temp.A_ID AND c.B_ID = temp.B_ID

But now I get an error:

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'IDENTITY'

Is there a clean way to do this in SQL?

• Evan Miller: Obviously the real need is more complicated, table C also has a value column and for the missing rows I fill in a new value based off averages in tables A and B
• Dale K: If you use row_number instead of identity you might get what you want

Answer 1

How about this pattern, which I use for assigning new IDs in a dimension table without using IDENTITY or a sequence.

select
 coalesce(c.C_ID, 
 max(c.C_ID) over()
 + row_number() over (partition by c.C_ID order by a.A_ID, b.B_ID)) C_ID,
 a.A_ID,
 b.B_ID 
from #A a 
cross join #B b
left join #c c
 on c.A_ID = a.A_ID
 and c.B_ID = b.B_ID
order by C_ID;

outputs

C_ID A_ID B_ID
-------------------- ----------- -----------
1 1 1
2 1 2
3 2 1
4 2 2