SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 First MERGE attempt not working, please help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 10/03/2012 :  20:17:25  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

Eagle_f90
Constraint Violating Yak Guru

USA
422 Posts

Posted - 10/03/2012 :  21:28:21  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
I needed to add a second check in the ON area to make sure the matches was unique. I ended up adding AND t.IncludedSectionID = s.IncludedSectionID to the ON command.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000