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?
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