CODEWITHSUNDEEP
t-sqlmerge
elbillaf
elbillaf

Reputation: 1984

TSQL MERGE command appears to not actually merge

I'm studying the code here.

When I cut-n-paste the following code from there:

-- MERGE statement with the join conditions specified correctly.

USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

It produces the output they say, BUT it doesn't actually change the TARGET table. How do I get it to actually change the results of the TARGET table? Is there some kind of switch?

Upvotes: 0

Views: 103

Answers (1)

Martin Smith
Martin Smith

Reputation: 453327

Replace the ROLLBACK TRAN; with a COMMIT TRAN;

The target table is being changed hence you seeing the correct OUTPUT but then you are undoing it in the next statement by rolling back the transaction.

Upvotes: 4

Related Questions