I recently had to do an UPSERT. (Yes, it’s a real word: UPdate + inSERT.) This would normally be an UPDATE followed by in INSERT wrapped in a transaction so if either fails it all rolls back. This means you will make at least two passes through the data, locking it until the transaction commits. Logically this is a single operation, wouldn’t it be nice to have a single construct that could handle the UPDATE, the INSERT, and maybe even a DELETE if necessary? That’s exactly what we have in the MERGE statement. SQL Books Online gives a good explanation of the syntax and a few examples but it can get very complex very quickly.
USE tempdb GO IF (OBJECT_ID('dbo.#Target')) IS NOT NULL DROP TABLE #Target; IF (OBJECT_ID('dbo.#Source')) IS NOT NULL DROP TABLE #Source; --Dupsert this table... CREATE TABLE #Target ( TargetID INTEGER IDENTITY PRIMARY KEY NOT NULL ,DataCol CHAR(10) NOT NULL ); INSERT INTO #Target (DataCol) VALUES ('Static'),('Update Me') ,('Delete Me') ; --...with changes from this one. CREATE TABLE #Source ( SourceID INTEGER IDENTITY(101,1) PRIMARY KEY NOT NULL ,TargetID INTEGER NULL ,DataCol CHAR(10) NOT NULL ); INSERT INTO #Source (DataCol,TargetID) VALUES ('Static',1),('Change',2),('New',NULL); --View tables SELECT * FROM #Target; SELECT * FROM #Source; --Dupsert the target using MERGE MERGE INTO #Target USING #Source ON #Target.TargetID = #Source.TargetID WHEN MATCHED THEN UPDATE SET #Target.DataCol = #Source.DataCol WHEN NOT MATCHED BY TARGET THEN INSERT (DataCol) VALUES (#Source.DataCol) WHEN NOT MATCHED BY SOURCE THEN DELETE; --View target SELECT * FROM #Target;
As you can see, the code is a logical unit, straightforward, and flexible. Enjoy!