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