CODEWITHSUNDEEP
sqlsql-server
s p
s p

Reputation: 799

SQL Compare rows of a table with multiple columns

I have a table T1

Id Col1 Col2 IsActive
1 T1 v1 1
2 T2 v2 0

Now received the following data and it need to be inserted into the above table.

|  Col1  |  Col2  |  
|--------|--------|
|   T1   |   v1   |
|   T2   |   v2   |
|   T3   |   v3   |

As this data contains some duplicated values, it need to be inserted based on IsActive Column value. For row with IsActive 1, need to insert with IsActive 2 and for row with IsActive 0, need to insert with IsActive 1 like below, the unique data need to be inserted with IsActive 1 and that is not a problem right now

Id Col1 Col2 IsActive
1 T1 v1 1
2 T2 v2 0
3 T1 v1 2
4 T2 v2 1
5 T3 v3 1

I have created a Temp table #Temp and inserted common rows in new incoming data and data from existing table like below:

#Temp

Col1 Col2 IsActive
T1 v1 1
T2 v2 0
T1 v1 NULL
T2 v2 NULL

Using Group By I can able to select duplicate rows but I need to insert based on IsActive value, so I stuck here.

insert into T1
select Col1, Col2, '1' from #Temp
GROUP BY Col1, Col2
HAVING COUNT(Col1) > 1

I need help on this above part, thanks in advance

Upvotes: 1

Views: 168

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:
    
-- create temp table with values to be inserted
INSERT INTO #ToInsert
    ([Col1], [Col2])
VALUES
    ('T1', 'v1'),
    ('T2', 'v2'),
    ('T3', 'v3')

-- join each value of the temp table to the original table. if
-- value exists increment its `IsActive` by 1, otherwise set it to 1
INSERT INTO t (Col1, Col2, IsActive)
SELECT i.Col1, i.Col2, COALESCE(t.IsActive + 1, 1) AS IsActive
FROM #ToInsert i
LEFT JOIN (
   SELECT Col1, Col2, max(IsActive) as IsActive
   FROM t 
   GROUP BY Col1, Col2
) t ON i.Col1 = t.Col1 AND i.Col2 = t.Col2

Demo here

Upvotes: 2

Related Questions