The SQL Server MERGE command is the combination of INSERT, UPDATE and DELETE commands consolidated into a single statement. Here is how to get started with the use MERGE into in SQL Server:
- Start off by identifying the target table which will be used in the logic.
- Next identify the source table which will be used in the logic.
- Determine the appropriate search conditions in the ON clause in order to match rows.
- Specify logic when records are matched or not matched between the target and source i.e. comparison conditions.
- For each of these comparison conditions code the logic. When matched, generally an update condition is used. When not matched, generally an insert or delete condition is used.
However, SQL Server provides the MERGE statement that allows you to perform three actions at the same time. The following shows the syntax of the MERGE statement:
MERGE target_table USING source_table
WHEN NOT MATCHED
WHEN NOT MATCHED BY SOURCE
MERGE INTO DepotDataTarget S
USING (SELECT MonthDate,TargetVal,TerritoryCode FROM @TargetType) T
ON (S.TerritoryCode = T.TerritoryCode AND S.MonthDate=T.MonthDate)
WHEN MATCHED THEN
UPDATE SET S.TargetVal = T.TargetVal,S.ModifiedDate=GETDATE()
WHEN NOT MATCHED THEN
INSERT (MonthDate,TargetVal,TerritoryCode,DoDate )