Monday, January 25, 2010

Merge Query W/O Using Merge


DECLARE @tblSource Table ( ID INT, GroupID INT, Name VARCHAR(10) )
DECLARE @tblTarget Table ( ID INT, GroupID INT, Name VARCHAR(10) )

INSERT INTO @tblSource VALUES (1,1,'a1')
INSERT INTO @tblSource VALUES (2,1,'a2')
INSERT INTO @tblSource VALUES (3,1,'a3')
INSERT INTO @tblSource VALUES (4,1,'a4')

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')

-----Delete Record
SELECT *
FROM @tblSource AS tblSource
FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NULL
AND tblTarget.ID IS NOT NULL
AND tblTarget.GroupID = 1

-----Update Record
SELECT *
FROM @tblSource AS tblSource
FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NOT NULL
AND tblTarget.ID IS NOT NULL

-----Inserted Record
SELECT *
FROM @tblSource AS tblSource
FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE
tblSource.ID IS NOT NULL
AND tblTarget.ID IS NULL
AND tblSource.GroupID = 1

--SELECT *
--FROM @tblSource AS tblSource
-- LEFT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT *
--FROM @tblSource AS tblSource
-- RIGHT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT *
--FROM @tblSource AS tblSource
-- FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

No comments: