Search code examples
sqlsql-serversql-updatedifference

Find difference between two table in SQL and append back result to source table & update column values only for newly inserted rows

I am new to SQL Server. I want to create a procedure which should check difference between master table & quarterly table and insert the different rows back to the master table and update the corresponding column values.

Master table is like:

|PID | Release_date | Retired_date

|loc12|202108 |
|loc34|202108 |

Quaterly table is like:

|PID | Address | Post_code

|loc12| Srinagar | 5678
|loc34| Girinagar | 6789
|loc45| RRnagar | 7890
|loc56| Bnagar | 9012

Resultant Master table should be like:

|PID | Release_date | Retired_date

|loc12|202108 |
|loc34|202108 |
|loc45|202111 |
|loc56|202111 |

I have tried except but I'm not able to update the master table after inserting the difference. My code is

insert into master(select PID from Master
                   except
                   select PID from Quaterly)
update master
set Release_date = '202111'
where PID in (select PID from Master
              except
              select PID from Quaterly)

TIA

Solution

  • You could do everything in one query, no need to use UPDATE:

    INSERT INTO Master(PID, Release_date)
SELECT q.PID, '202111'
FROM Quaterly q
WHERE q.PID NOT IN (SELECT PID FROM Master)