April 24, 2015 at 1:20 am
Hi,
I have created a stored procedure to load incremental data from Import table to Archive Table.
Below scenario will be handled in stored procedure.
1.If target Archive table is not in sync with source Import table then find the column differences and update Archive table to match.
2.There are (insert number here) rows missing from Archive table. Find them in Import table and insert them.
3.The rows in Archive table have been updated. Synchronize them with Import table.
I have used MERGE statement to implement SCD as described below. Syntax for the same is as below.
MERGE "to an Archive Database “[Destination]
USING "data from a Data staging Database" [Source]
WHEN MATCHED "update existing data"
WHEN NOT MATCHED ON SOURCE "insert new data"
Working of Store procedure
There are 2 scenario covered in this procedure on the basis of that ETL happening.
There are 2 columns derived from source table at run time
1)BBxkey which is a combination of one or more column or part of column
2)Another column is a Rowchecksum column which is a Hashvalue of all the column in a table for a particular row.
Merge case 1:-WHEN NOT MATCH THEN INSERT
If source BBxkey is not match with Archive table then those records are new and it will directly inserted into Archive table.
Merge case 2:-WHEN MATCH THEN UPDATE
If source records are available in Archive table but some of the record in source column is change, then update the old record with latestversion=0 and insert the new record with latestversion =1
Below condition is used to checj whether the records need to be updated (Source.BBxkey=Target.BBxkey && Source.Rowchecksum<>Target.Rowchecksum)
Stored procedure:-
DECLARE @Merge_Out TABLE (Action_Taken varchar(8),
TimeIn datetime,
BBXKey varchar(100),
RowChecksum nvarchar(4000),Col001 varchar(8000),Col002 varchar(8000),
Col003 varchar(8000),Col004 varchar(8000),Col005 varchar(8000),
Col006 varchar(8000),Col007 varchar(8000),Col008 varchar(8000),
Col009 varchar(8000),Col010 varchar(8000), LatestVersion bit)
MERGE [Archive].[DBO].[ArchiveBBXFBFLG] T
USING (
SELECT Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010,
CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column(''md5hash'')))', 'VARCHAR(MAX)') RowChecksum,
SUBSTRING(Col001,1,27) bbxkey
FROM (
SELECT Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010,
HASHBYTES('MD5',CAST(CONCAT(Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010,'') as nvarchar(max))) md5hash
FROM [DataStaging].[DBO].[ImportBBxFBFLG] ) as tab ) S
ON (S.BBxKey = T.BBxKey AND (T.LatestVersion = 1 OR T.LatestVersion IS NULL))
WHEN NOT MATCHED BY TARGET THEN
INSERT (Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010,TimeIn ,BBXKey,RowChecksum,LatestVersion)
VALUES (Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010,getdate(),BBXKey,RowChecksum,1)
WHEN MATCHED AND T.latestversion = 1 AND s.RowChecksum <> T.RowChecksum THEN
UPDATE SET T.LatestVersion = 0
OUTPUT $ACTION Action_Taken, GETDATE() TimeIn,S.BBXKey,S.RowChecksum,S.Col001,S.Col002,S.Col003,S.Col004,S.Col005,S.Col006,S.Col007,S.Col008,S.Col009,S.Col010,1 AS LatestVersion
INTO @MERGE_OUT ;INSERT INTO [Archive].[DBO].ArchiveBBXFBFLG(TimeIn ,BBXKey,RowChecksum,LatestVersion,Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010)
SELECT TimeIn ,BBXKey,RowChecksum,LatestVersion,Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010 from @merge_out
where Action_Taken = 'UPDATE';
I am getting belwo error when duplicate bbxkey found in source table.
“Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”
Is there any way in merge itself to avide this issue.
regards,
Vipin jha
April 24, 2015 at 1:27 am
Is there any way in merge itself to avide this issue.
Sure. Always match on a unique key (must be unique in both source and target).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2015 at 2:23 am
yes true, but if in case we duplicate bbxkey come in source then how to avoid this .
April 24, 2015 at 2:32 am
vipin_jha123 (4/24/2015)
yes true, but if in case we duplicate bbxkey come in source then how to avoid this .
De-duplicate your source before merging, or find a better match key.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 24, 2015 at 2:59 am
Ok,
Is there any alternate of merge where i should not get issue with duplicate record in source if found.
April 24, 2015 at 3:33 am
Also note that this requrement
If source records are available in Archive table but some of the record in source column is change, then update the old record with latestversion=0 and insert the new record with latestversion =1
cannnot be implemented using MERGE. Even when both source and target have proper unique keys to match, any pair of source/target raws can not be MATCHED and NOT MATCHED at the same time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply