--JA: MERGE source with destination
MERGE dbo.DimCustomer Destination
USING --JA: source
	(
	SELECT
		DC.[HistoryCol1]
		,DC.[UpdateOnlyCol1]
		,DC.[HistoryCol2]
		,DC.[HistoryCol3]
		,DC.[UpdateOnlyCol2]
		,DC.[StaticCol1]
	FROM dbo.DimCustomerStage DC
	)
AS [Source]
	(
	[HistoryCol1]
	,[UpdateOnlyCol1]
	,[HistoryCol2]
	,[HistoryCol3]
	,[UpdateOnlyCol2]
	,[StaticCol1]
	)
ON (
	[Source].[HistoryCol1]		= Destination.[HistoryCol1]
	AND [Source].[HistoryCol2]	= Destination.[HistoryCol2]
	AND [Source].[HistoryCol3]	= Destination.[HistoryCol3]
	)
WHEN MATCHED --JA: here i specify that in order to qualify for update, the binary sum of the two rows must not be equal (a change has occured in source) - checksum is faster than checking every column individually
	AND BINARY_CHECKSUM
			(
			Destination.[UpdateOnlyCol1]
			,Destination.[UpdateOnlyCol2]
			)
		!= BINARY_CHECKSUM
			(
			[Source].[UpdateOnlyCol1]
			,[Source].[UpdateOnlyCol2]
			)
	THEN UPDATE SET --JA: put only the columns you wish to update here - less = better performance
		Destination.[UpdateOnlyCol1]	= [Source].[UpdateOnlyCol1]
		,Destination.[UpdateOnlyCol2]	= [Source].[UpdateOnlyCol2]
WHEN NOT MATCHED BY TARGET THEN --JA: when no matching id can be found, insert
	INSERT
		(
		[HistoryCol1]
		,[UpdateOnlyCol1]
		,[HistoryCol2]
		,[HistoryCol3]
		,[UpdateOnlyCol2]
		,[StaticCol1]
		)
	VALUES
		(
		[Source].[HistoryCol1]
		,[Source].[UpdateOnlyCol1]
		,[Source].[HistoryCol2]
		,[Source].[HistoryCol3]
		,[Source].[UpdateOnlyCol2]
		,[Source].[StaticCol1]
		)
; --JA: MERGE must be terminated by a semicolon

MERGE INTO dbo.Client_SCD2 AS DST
USING dbo.Client AS SRC
ON SRC.ID = DST.BusinessKey
AND DST.ClientName=SRC.ClientName
AND DST.Country = SRC.Country
AND DST.Town=SRC.Town
AND DST.Address1=SRC.Address1
AND DST.Address2=SRC.Address2
AND DST.ClientType=SRC.ClientType
AND DST.ClientSize=SRC.ClientSize

WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET DST.IsCurrent = 0, DST.ValidTo = @Yesterday

WHEN MATCHED 
AND IsCurrent = 1
AND (
 ISNULL(DST.ClientName,'') <> ISNULL(SRC.ClientName,'') 
 OR ISNULL(DST.Country,'') <> ISNULL(SRC.Country,'') 
 OR ISNULL(DST.Town,'') <> ISNULL(SRC.Town,'')
 OR ISNULL(DST.Address1,'') <> ISNULL(SRC.Address1,'')
 OR ISNULL(DST.Address2,'') <> ISNULL(SRC.Address2,'')
 OR ISNULL(DST.ClientType,'') <> ISNULL(SRC.ClientType,'')
 OR ISNULL(DST.ClientSize,'') <> ISNULL(SRC.ClientSize,'')
 )

MERGE INTO dbo.Client_SCD2 AS DST
USING dbo.Client AS SRC
ON SRC.ID = DST.BusinessKey
AND DST.ClientName=SRC.ClientName
AND DST.Country = SRC.Country
AND DST.Town=SRC.Town
AND DST.Address1=SRC.Address1
AND DST.Address2=SRC.Address2
AND DST.ClientType=SRC.ClientType
AND DST.ClientSize=SRC.ClientSize
WHEN NOT MATCHED BY TARGET THEN 
INSERT (BusinessKey, ClientName, Country, Town, County, Address1, Address2, ClientType, ClientSize, ValidFrom, IsCurrent)
VALUES (SRC.ID, SRC.ClientName, SRC.Country, SRC.Town, SRC.County, Address1, Address2, ClientType, ClientSize, @Today, 1)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET DST.IsCurrent = 0, DST.ValidTo = @Yesterday;