February 16, 2015 at 11:30 pm
Hi,
When I am updating any record in source , and executing my procedure its gets updated in destination table.
but not inserting a new record with latestversion flag 0.
when I am executing same store procedure again its inserting new record.
It should update and insert in single run .
My SP Code:
ALTER PROCEDURE [dbo].[ETL_STAGE_ARCHIVE_FDSRG]
AS
--Version 0.2(latest)
BEGIN
SET NOCOUNT ON
DECLARE @Source_RowCount int
DECLARE @New_RowCount int
DECLARE @updated_Rowcount int
DECLARE @Matched_record int
DECLARE @TimeIn smalldatetime
DECLARE @LatestVersion int
SET NOCOUNT ON
SELECT @TimeIn = GETDATE()
SELECT @LatestVersion = 1
SELECT @Source_RowCount = COUNT(1) FROM ImportBBxFDSRG ---To get source record count
DECLARE @rowcounts TABLE
(
mergeAction nvarchar(10)
);
MERGE Archive.dbo.ArchiveBBxFDSRG AS TARGET
USING (SELECT *,
CAST(SUBSTRING(Col001,1,2) AS varchar(100)) BBxKey,
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003) AS varchar(max))) RowChecksum
FROM dbo.ImportBBxFDSRG) AS SOURCE
ON SUBSTRING(SOURCE.Col001,1,2)= target.BBxKey
AND (target.LatestVersion = 1 OR target.LatestVersion IS NULL)
WHEN MATCHED AND (source.BBxKey = Target.BBxKey AND source.RowChecksum <> TARGET.RowChecksum) THEN
UPDATE SET
TARGET.TimeIn = @TimeIn,
TARGET.BBXKey = SOURCE.BBXKey,
TARGET.RowChecksum = SOURCE.RowChecksum,
TARGET.Col001 = SOURCE.Col001,
TARGET.Col002 = SOURCE.Col002,
TARGET.Col003 = SOURCE.Col003,
TARGET.LatestVersion = 0
WHEN NOT MATCHED THEN --For New record
INSERT (TimeIn, BBXKey, RowChecksum, Col001, Col002,Col003, LatestVersion)
VALUES (GETDATE(), SOURCE.BBXKey, SOURCE.RowChecksum, SOURCE.Col001, SOURCE.Col002,SOURCE.Col003, @LatestVersion)
OUTPUT $action into @rowcounts;
SELECT @Source_RowCount Source_Rowcount,
SUM(case when mergeaction = 'INSERT' THEN 1 ELSE 0 END) AS INSERTED
,sum(case when mergeaction = 'UPDATE' THEN 1 ELSE 0 END) AS UPDATED
FROM @rowcounts
END
Thanks in Advance
regards,
Vipin jha
February 17, 2015 at 1:24 am
Why would it do an update and the insert at the same time (for a specific row).
In a MERGE statement, a row either matches or it doesn't match. It cannot be both at the same time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 17, 2015 at 1:58 am
Hi Thanks for your update.
Is there any way where I can have the history with version 0 and new with version 1
Regards,
Vipin jha
February 17, 2015 at 2:06 am
It's possible to handle type 2 SCD with the MERGE statement.
I describe it here:
Four Methods for Implementing a Slowly Changing Dimension in Integration Services – Part II[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply