Find difference between two table in SQL and append back result to source table & update column valu-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

Other approach you can use by leveraging SQL JOINs:

INSERT INTO MASTER
SELECT q2.PID, '202111'
FROM Quaterly q1
LEFT JOIN Quaterly q2
ON q1.PID = q2.PID
WHERE q1.PID IS NULL