Sql query to update and insert records without deleting and inserting into a table in sql server

Alternative way to update a table without deleting and inserting in sql sever

Suppose we have two tables: tblSource and tblTarget.

tblSource

CREATE TABLE tblSource(

    ID INT IDENTITY PRIMARY KEY,

    vcData VARCHAR(500)

)

INSERT INTO tblSource VALUES('PC'),('Laptop'),('Mobile')

ID	vcData
11	PC
12	Laptop
13	Mobile

tblTarget.

CREATE TABLE tblTarget(

    ID INT IDENTITY PRIMARY KEY,

    vcData VARCHAR(500)

)

INSERT INTO tblTarget VALUES('Laptop')

ID	vcData
6	Laptop

Now we want transmit the data from tblSource to tblTarget without first deleting from tblTarget then inserting into it from tblSource.

SQL query:

WITH cteTarget

AS(

    SELECT

         *,

         ROW_NUMBER() OVER(ORDER BY ID) AS TID

    FROM tblTarget

)

MERGE cteTarget

USING(

    SELECT

         vcData,

         ROW_NUMBER() OVER(ORDER BY ID) AS SID

         FROM tblSource

) AS tblSource(vcData,SID)

ON cteTarget.TID =  tblSource.SID

WHEN MATCHED THEN

    UPDATE SET vcData = tblSource.vcData

WHEN NOT MATCHED BY SOURCE THEN

    DELETE

WHEN NOT MATCHED BY TARGET THEN

    INSERT(vcData) VALUES(tblSource.vcData);

After executing this query data in the tblSource will be:

ID	vcData
6	PC
7	Laptop
8	Mobile

Explanation:

Now I am going to explain how this query work. I have created one pseudo column (Or rwo id) in both tblSource and tblTarget. Which will look some thing like this:

tblSource

ID	vcData	SID
11	PC	1
12	Laptop	2
13	Mobile	3

tblTarget(cteTarget)

ID	vcData	TID
6	Laptop	1

This query will compare SID and TID. If SID is equal to TID then it will update the vcData column of tblTarget. That is "Laptop" will be updated by "Pc". If SID is not equal to TID then if value of SID is not present in TID then it will insert the records into the tblTarget. That is  row of SID 2 and 3 will be inserted into tblTarget. And if value of TID is not in SID then that rows will be deleted from tblTarget.