Saturday, June 5, 2010

Merge Statement

 

SQL Statement:

DECLARE @tblMergeOutput TABLE
(
ID BIGINT IDENTITY(1,1)
, MergeAction NVARCHAR(100)
, InsertedId INT
, DeletedId INT
, GroupID INT
)

CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

INSERT INTO #tblSource VALUES (1,1,'a11')
INSERT INTO #tblSource VALUES (2,1,'a21')
INSERT INTO #tblSource VALUES (3,1,'a31')
INSERT INTO #tblSource VALUES (4,1,'a41')

INSERT INTO #tblTarget VALUES (1,1,'a1')
INSERT INTO #tblTarget VALUES (3,1,'a3')
INSERT INTO #tblTarget VALUES (5,1,'a5')
INSERT INTO #tblTarget VALUES (6,2,'b2')
INSERT INTO #tblTarget VALUES (7,2,'b1')

MERGE #tblTarget
USING (
SELECT *
FROM #tblSource
) AS tblSource
ON tblSource.GroupID = #tblTarget.GroupID
AND tblSource.ID = #tblTarget.ID
WHEN MATCHED THEN
UPDATE
SET Name = tblSource.Name
WHEN NOT MATCHED THEN
INSERT (
ID
, GroupID
, Name
)
VALUES (
tblSource.ID
, tblSource.GroupID
, tblSource.Name
)
WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
THEN DELETE
OUTPUT
$action
, inserted.Id
, deleted.Id
, tblSource.GroupID
INTO @tblMergeOutput;

SELECT * FROM #tblSource
SELECT * FROM #tblTarget order by GroupID, ID
SELECT * FROM @tblMergeOutput

DROP TABLE #tblSource
DROP TABLE #tblTarget
 
Target Table Output:
 ------------------
ID GroupID Name
------------------
1 1 a11
2 1 a21
3 1 a31
4 1 a41
6 2 b2
7 2 b1
------------------
 
Merge Table Output:
----------------------------------------------------
ID Merge Action InsertedID DeletedID GroupID
----------------------------------------------------
1 INSERT 2 NULL 1
2 INSERT 4 NULL 1
3 UPDATE 1 1 1
4 UPDATE 3 3 1
5 DELETE NULL 5 NULL
----------------------------------------------------

0 comments:

Post a Comment