I would like to ensure I have a unique id for each combination of two other ids. Some combinations already exist, so 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

RESULT:
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword ‘IDENTITY’.

Is there a clean way to do this in SQL?