Combining MERGE with EXISTS and EXCEPT
By combining EXISTS
with EXCEPT
you can quickly and efficiently compare whether two columns are equal (including when they are both NULL
). Using this method if the column on both sides is NULL
then they will be treated as equal; and if one side is NULL
and the other not, they will be correctly treated as not equal.
Example:
SELECT ... FROM ...
WHERE EXISTS( SELECT [Column_A] EXCEPT SELECT [Column_B] );
The biggest power of this is realised when combining it with the MERGE
statement. By using AND EXISTS(SELECT ... EXCEPT SELECT ...)
within the WHEN MATCHED ...
portion of the merge, SQL automatically checks for NULL
’s on both sides. This is more performant than using the not equal to operator with ISNULL
e.g. ISNULL(...,'') <> ISNULL(...,'')
, as SQL can now take advantage of any indexes that exist.
MERGE INTO [TargetTable] AS [T]
USING [SourceTable] AS [S]
ON ([T].[ID] = [S].[ID])
WHEN MATCHED AND EXISTS (
SELECT
[T].[Code]
,[T].[Description] COLLATE Latin1_General_CS_AS
EXCEPT SELECT
[S].[Code]
,[S].[Description] COLLATE Latin1_General_CS_AS
) THEN
UPDATE SET
[T].[Code] = [S].[Code]
,[T].[Description] = [S].[Description]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([ID], [Code], [Description])
VALUES ([ID], [Code], [Description])
WHEN NOT MATCHED BY SOURCE THEN DELETE;