SCD Type 2 : MERGE Update and Insert

Question

Hi All - I am trying to implement a Type 2 Slowly changing dimension in SQL procedure using MERGE.

Here are the rules :

1. If its a new record insert into target table with start date = getdate, end date = null and islatest =1
2. If the record is deleted in source and it exists in target then set the record in target with endate = getdate()-1 and islatest = 0
3. If any of the attribute is changed based on the source target join then it should be an upsert ( update the old record with end date = getdate()-1 and islatest = 0) and insert the latest record with the new changed attribute with start date = getdate(), end date = NULL and islatest =1.

I need help with 2 and 3.

below is what i have written but i am not sure how to write the condition of "insert the latest record with the new changed attribute with start date = getdate(), end date = NULL and islatest =1." Could you please help with this ?

MERGE [CDW].[dbo].[DF_SERVICE_CONTRACTS_PRICING_HISTORY] TARGET --History
USING #ServiceContractsPricing AS SOURCE --Oracle
--Unique Key for Matching
	ON (CAST(TARGET.Price_list_line_id as DECIMAL) = CAST(SOURCE.Price_list_line_id as DECIMAL)
		AND CAST(TARGET.currency_detail_id as DECIMAL) = CAST(SOURCE.currency_detail_id as DECIMAL))
--If its a new record insert into target table with start date = getdate, end date = null and islatest =1
WHEN NOT MATCHED 
	THEN
		INSERT (
			[price_list_header_id]
			,[price_list_line_id]
			,[price_list_name]
			,[item_number]
			,[item_id]
			,[list_price]
			,[price_list_currency]
			,[price_list_uom_code]
			,[start_date_active]
			,[end_date_active]
			,[conversion_name]
			,[conversion_description]
			,[conversion_currency_code]
			,[conversion_rate]
			,[conversion_start_date]
			,[conversion_end_date]
			,[conversion_type]
			,[currency_detail_id]
			,IsLatest
			,[Start_date]
			)
		VALUES (
			SOURCE.price_list_header_id
			,SOURCE.price_list_line_id
			,SOURCE.price_list_name
			,SOURCE.item_number
			,SOURCE.item_id
			,SOURCE.list_price
			,SOURCE.price_list_currency
			,SOURCE.price_list_uom_code
			,SOURCE.start_date_active
			,SOURCE.end_date_active
			,SOURCE.conversion_name
			,SOURCE.conversion_description
			,SOURCE.conversion_currency_code
			,SOURCE.conversion_rate
			,SOURCE.conversion_start_date
			,SOURCE.conversion_end_date
			,SOURCE.conversion_type
			,SOURCE.currency_detail_id
			,1
			,GETDATE()
		
			)

		/*
If any of the attribute is changed based on the source target join then it should be an upsert ( update the old record with end date = getdate()-1 and islatest = 0) 
and insert the latest record with the new changed attribute with start date = getdate(), end date = NULL and islatest =1.
		*/
WHEN MATCHED AND IsLatest =1 
--AND End_date = 9999-12-31
	AND (ISNULL(TARGET.price_list_header_id,'') <> ISNULL(SOURCE.price_list_header_id,'')
	OR ISNULL(TARGET.price_list_name,'') <> ISNULL(SOURCE.price_list_name,'')
	OR ISNULL(TARGET.item_number,'') <> ISNULL(SOURCE.item_number,'')
	OR ISNULL(TARGET.item_id,'') <> ISNULL(SOURCE.item_id,'')
	OR ISNULL(TARGET.list_price,'') <> ISNULL(SOURCE.list_price,'')
	OR ISNULL(TARGET.price_list_currency,'') <> ISNULL(SOURCE.price_list_currency,'')
	OR ISNULL(TARGET.price_list_uom_code,'') <> ISNULL(SOURCE.price_list_uom_code,'')
	OR ISNULL(TARGET.start_date_active,'') <> ISNULL(SOURCE.start_date_active,'')
	OR ISNULL(TARGET.end_date_active,'') <> ISNULL(SOURCE.end_date_active,'')
	OR ISNULL(TARGET.conversion_name,'') <> ISNULL(SOURCE.conversion_name,'')
	OR ISNULL(TARGET.conversion_description,'') <> ISNULL(SOURCE.conversion_description,'')
	OR ISNULL(TARGET.conversion_currency_code,'') <> ISNULL(SOURCE.conversion_currency_code,''))

	--UPDATE the record. Expire the record by setting IsLatest=0 and EndDate=getdate()
	THEN 
		UPDATE
		SET 
		--TARGET.price_list_header_id = SOURCE.price_list_header_id
		--	,TARGET.price_list_name = SOURCE.price_list_name
		--	,TARGET.item_number = SOURCE.item_number
		--	,TARGET.item_id = SOURCE.item_id
		--	,TARGET.list_price = SOURCE.list_price
		--	,TARGET.price_list_currency = SOURCE.price_list_currency
		--	,TARGET.price_list_uom_code = SOURCE.price_list_uom_code
		--	,TARGET.start_date_active = SOURCE.start_date_active
		--	,TARGET.end_date_active = SOURCE.end_date_active
		--	,TARGET.conversion_name = SOURCE.conversion_name
		--	,TARGET.conversion_description = SOURCE.conversion_description
		--	,TARGET.conversion_currency_code = SOURCE.conversion_currency_code
		    TARGET.Islatest =0
			,TARGET.end_date = getdate()-1

			 

 --If the record is deleted in source and it exists in target then set the record in target with endate = getdate()-1 and islatest = 0
WHEN NOT MATCHED BY SOURCE 
	THEN UPDATE
		SET TARGET.end_date = getdate()
		,TARGET.islatest=0;

Answer 1

You seem to have #2 correct.

Step #3 on the other hand cannot be done in a single MERGE like that without extra trickery, since from one source row, you want to do both an INSERT and an UPDATE. I can think of ways of doing this in the MERGE, but they would be advanced. (And I would have to test, which I don't feel like for the moment.)

Instead, just do the update of the old row. Then use the OUTPUT clause to save the values of the source table. Also add the $ACTION column to the OUTPUT clause. Those with $ACTION = 'UPDATE' are the ones you want to INSERT.

Actually, you need that intermediate table, but you can use composable DML like this:

INSERT tbl (....)
SELECT ...
FROM   (MERGE ...
            OUTPUT $ACTION, SOURCE.col1, SOURCE.col2, ....) AS M
WHERE M."$ACTION""= 'UPDATE'

But, again, I feel that this is more on the complex side of things.

Answer 2

Hi @kkran

If the record is deleted in source and it exists in target then set the record in target with endate = getdate()-1 and islatest = 0

As I know, it is not Merge used to detected records deleted in source table, it sounds more like a trigger.

Best regards,

Cosmog Hong