sqlsql-serversubquerycross-join

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


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?


Solution

  • 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