Target Table:

TargetTable(ID, Code, Number, Description, Type)

Source Table:

SourceTable(ID, Code, Number, Description, Type)

SQL Results Table: Use this table to store the results incase you need to have the values for some period of time.

#SQLResults([OPACTION] VARCHAR(10), [Code_I] BIGINT, [Number_I] VARCHAR(10), [Type_I] VARCHAR(3), [Code_U] BIGINT, [Number_U] VARCHAR(10), [Type_U] VARCHAR(3))

SQL Merge Statement

 MERGE

                TargetTable T

USING

                SourceTable S

ON

                T.Code = S.Code

AND

                T.Number = S.Number

AND

                T.Type = S.Type

AND

                T.Number = S.Number

WHEN NOT MATCHED BY TARGET

THEN INSERT

                (Code,Number,Description,Type)

VALUES

                (S.Code,S.Number,S.Description,S.Type)

WHEN MATCHED

THEN UPDATE SET

                T.Description = S.Description

 

OUTPUT $ACTION, INSERTED.Code, INSERTED.Number, INSERTED.Type, DELETED.Code, DELETED.Number, DELETED.Type INTO #SQLResults;

 

SELECT COUNT(*) FROM #SQLResults WHERE [OPACTION] = 'INSERT'

SELECT COUNT(*) FROM #SQLResults WHERE [OPACTION] = 'UPDATE'