AuthenticReplica AuthenticReplica - 5 months ago 10
SQL Question

Adding a unique ID number to a difference of tables

I'm using this query to find the difference in rows between 2 different databases (A and B) on the same table that I need to insert into B :

(select recordkey, class, relatedreckey, relatedrecclass, relatetype
from A.dbo.relatedrecord
group by recordkey, class, relatedreckey, relatedrecclass, relatetype)
EXCEPT
(select recordkey, class, relatedreckey, relatedrecclass, relatetype
from B.dbo.relatedrecord
group by recordkey, class, relatedreckey, relatedrecclass, relatetype)


The problem I'm facing now is that there is a unique id column called RELATEDRECORDID that I need to populate in a sequence (max(B.dbo.relatedrecordid)+1) for each of those result rows.

How can I include this unique ID generation in the original query?

Answer

Why don't you use a SQL Row_Number() function outside the select query. I mean place the two selects in a SQL CTE expression, and in the SELECT list add a new column for Row_Number() with Order By clause which will return a unique number in order for each row

For example you can modify below Select statement

;with cte as (

(select recordkey, class, relatedreckey, relatedrecclass, relatetype
from A.dbo.relatedrecord
group by recordkey, class, relatedreckey, relatedrecclass, relatetype)
EXCEPT
(select recordkey, class, relatedreckey, relatedrecclass, relatetype
from B.dbo.relatedrecord
group by recordkey, class, relatedreckey, relatedrecclass, relatetype)

)
select row_number() over (order by recordkey, class, relatedreckey, relatedrecclass, relatetype) rn, * from cte