Jan 27, 2014

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.

No comments:

Post a Comment