Tutorial by Examples

MERGE INTO targetTable USING sourceTable ON (targetTable.PKID = sourceTable.PKID) WHEN MATCHED AND (targetTable.PKID > 100) THEN DELETE WHEN MATCHED AND (targetTable.PKID <= 100) THEN UPDATE SET targetTable.ColumnA = sourceTable.ColumnA, targetTable.Co...

WITH SourceTableCTE AS ( SELECT * FROM SourceTable ) MERGE TargetTable AS target USING SourceTableCTE AS source ON (target.PKID = source.PKID) WHEN MATCHED THEN UPDATE SET target.ColumnA = source.ColumnA WHEN NOT MATCHED THEN INSERT (ColumnA) VALUES (Source.ColumnA);...

MERGE INTO TargetTable AS Target USING (VALUES (1,'Value1'), (2, 'Value2'), (3,'Value3')) AS Source (PKID, ColumnA) ON Target.PKID = Source.PKID WHEN MATCHED THEN UPDATE SET target.ColumnA= source.ColumnA WHEN NOT MATCHED THEN INSERT (PKID, ColumnA) VALUES (Source.PKID...

To Illustrate the MERGE Statement, consider the following two tables - dbo.Product : This table contains information about the product that company is currently selling dbo.ProductNew: This table contains information about the product that the company will sell in the future. The foll...

Use EXCEPT to prevent updates to unchanged records MERGE TargetTable targ USING SourceTable AS src ON src.id = targ.id WHEN MATCHED AND EXISTS ( SELECT src.field EXCEPT SELECT targ.field ) THEN UPDATE SET field = src.field WHEN...