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?

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