My first attempt at a MERGE run is not throughing any errors but it is not merging the data like I want. Can anyone tell me what I am doing wrong? The point is to compair #Temp and #tblCollectionGameList and update #tblCollectionGameList so that it contains only the items inside #Temp (inserting missing items into tblCollecitonGameList and deleteing items from tblColletionGameList that are no longer in #Temp)
CREATE TABLE #Temp
(
CollectionID smallint NOT NULL,
IncludedSectionID smallint NOT NULL
)
INSERT INTO #Temp (CollectionID, IncludedSectionID) VALUES(0, 0);
INSERT INTO #Temp (CollectionID, IncludedSectionID) VALUES(0, 1);
SELECT * FROM #Temp;
CREATE TABLE #tblCollectionGameList
(
ID smallint IDENTITY(0,1) NOT NULL,
CollectionID smallint NOT NULL,
IncludedSectionID smallint
)
INSERT INTO #tblCollectionGameList (CollectionID, IncludedSectionID) VALUES(0,0);
INSERT INTO #tblCollectionGameList (CollectionID, INcludedSectionID) VALUES(0,2);
SELECT * FROM #tblCollectionGameList;
BEGIN TRAN
MERGE #tblCollectionGameList AS t
USING #Temp AS s
ON (t.CollectionID = s.CollectionID)
WHEN NOT MATCHED BY TARGET
THEN
INSERT(CollectionID, IncludedSectionID) VALUES (s.CollectionID, s.IncludedSectionID)
WHEN NOT MATCHED BY SOURCE
THEN
DELETE;
SELECT * FROM #tblCollectionGameList;
ROLLBACK TRAN
DROP TABLE #Temp;
DROP TABLE #tblCollectionGameList;
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
First MERGE attempt not working, please help
Posted - 10/03/2012 : 20:17:25
