SQL : Select all column after a distinct count = 1

In sql, I am trying to do that :

I have a (staging) table with let say 8 columns

4 of these column are primary key of main table in which I need to insert the data of staging table

1 column is the operation of the column (I for Insert, U for Update, D for Delete)

3 are the data

it means that for 1 primary key of the main table, I can have multiple column for multiple operation in staging one (for example, if I have a insert, then a update)

What I am trying to do is first select all the column where for a primary key, there is only 1 record in the staging table, and from that select the one that are Insert. I have that :

SELECT [KEY1], [KEY2], [KEY3], [KEY4], COUNT(1) AS [COUNT] 
INTO #COUNTTABLE
FROM staging
GROUP BY [KEY1], [KEY2], [KEY3], [KEY4]

SELECT [KEY1], [KEY2], [KEY3], [KEY4]
INTO #KEYTABLE
FROM #COUNTTABLE 
WHERE [COUNT]=1

With that I have the list of keys that appear only 1 time in my staging table. What I want now is from this list, select all the data from staging where Operation=’I’

So something like that :

SELECT * FROM staging WHERE ([KEY1], [KEY2], [KEY3], [KEY4]) in #KEYTABLE AND [Operation]='I'

but I have not the exact syntaxe to do something like that, what is the best solution to do that?

I can do a while loop, but it takes to many times for big table (I have try this solution first, meaning loop over all the row of the staging table and do the operation, but it takes to many times, so trying to optimize the operation by firstly doing the bulk insert of row that appear one times in staging, it is the majority of row so it will improve a lot the process) Thanks

Answer

use this :

with cte as (
select ROW_NUMBER() over (partition by [KEY1], [KEY2], [KEY3], [KEY4] order by [KEY1], [KEY2], [KEY3], [KEY4])rnum, * 
from staging )
select * from  cte s 
left join (select * from  cte  where rnum = 2)c on  s.[KEY1]=c.[KEY1] and  s.[KEY2]=c.[KEY2] and  s.[KEY3]=c.[KEY3] and  s.[KEY4] =c.[KEY4] 
where c.KEY1 is null

Advertisement

Answer