Just playing around with SCD4 as I've used a different approach previously
(5 row(s) affected)
(0 row(s) affected)
Msg 515, Level 16, State 2, Line 60
Cannot insert the value NULL into column 'BusinessKey', table 'SQLServerCentral.dbo.Client_SCD4_History'; column does not allow nulls. INSERT fails.
The statement has been terminated.
So, I've dropped in a selection from @Client_SCD4 and run up to this point:
OUTPUT DELETED.BusinessKey, DELETED.ClientName, DELETED.Country, DELETED.Town, DELETED.Address1, DELETED.Address2, DELETED.ClientType, DELETED.ClientSize, $Action AS MergeAction
INTO @Client_SCD4 (BusinessKey, ClientName, Country, Town, Address1, Address2, ClientType, ClientSize, MergeAction)
select * from @Client_SCD4
This gives 5 rows of NULLS with this exception of merge action which is set to INSERT
I would have expected this to be blank as I'm not actually deleting anything on the first run
Am I correct?