Updatingthe destination but not inserting new row in destination table

Question

Post reply

Updatingthe destination but not inserting new row in destination table

vipin_jha123

Hall of Fame

Points: 3085
More actions
February 16, 2015 at 11:30 pm

#295422

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