Capture Source count,update count,inserted count in variable using merge

Question

Capture Source count,update count,inserted count in variable using merge

vipin_jha123

Hall of Fame

Points: 3085
More actions
February 11, 2015 at 11:06 pm

#301023

Hi ,
Below sp is working fine updating and inserting record properly.
But now I want to track the source record , updated record for logging in my variable.
please suggest me the same
alter Procedure SP_Archive_using_merge
AS
BEGIN
SET NOCOUNT ON
Declare @Source_RowCount int
Declare @New_RowCount int
Declare @updated_Rowcount int
DECLARE @TimeIn SMALLDATETIME
DECLARE @LatestVersion INT
SET NOCOUNT ON
---BBxKey and Hash value of all the source columns are derived in source query itself--
select @TimeIn=getdate()
Select @LatestVersion=1
MERGE Archive.dbo.ArchiveBBxCemxr as TARGET
USING
(select *,cast(SUBSTRING(Col001,1,10) as varchar(100)) BBxKey,
HashBytes('MD5', CAST(CHECKSUM(Col001,Col002,Col003,Col004,Col005,Col006,Col007) AS VARCHAR(MAX))) RowChecksum
from dbo.ImportBBxCemxr
where Col001 IS NOT NULL)as SOURCE
ON (SOURCE.Col001 = target.BBxKey )
AND (target.LatestVersion = 1 OR target.LatestVersion IS NULL)
WHEN MATCHED 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.Col004=SOURCE.Col004,
TARGET.Col005=SOURCE.Col005,
TARGET.Col006=SOURCE.Col006,
TARGET.Col007=SOURCE.Col007,
TARGET.LatestVersion=@LatestVersion
WHEN NOT MATCHED THEN --For New record
Insert (TimeIn,BBXKey,RowChecksum,Col001,Col002,Col003,Col004,Col005,Col006,Col007,LatestVersion)
values(getdate(),SOURCE.BBXKey,SOURCE.RowChecksum,SOURCE.Col001,SOURCE.Col002,SOURCE.Col003,SOURCE.Col004,SOURCE.Col005,SOURCE.Col006,SOURCE.Col007,@LatestVersion);
Select @New_RowCount=@@ROWCOUNT
end

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Use the OUTPUT clause and the $action column https://msdn.microsoft.com/en-us/library/ms177564.aspx you can then aggregate over that.

Note: I have found that it is a real performance hit and only do this if you really really really need to know 🙂

Clear Sky SQL
My Blog[/url]

Phil Parkin SSC Guru Points: 246978 More actions · Answer 2

Dave Ballantyne (2/12/2015)
Use the OUTPUT clause and the $action column https://msdn.microsoft.com/en-us/library/ms177564.aspx you can then aggregate over that.
Note: I have found that it is a real performance hit and only do this if you really really really need to know 🙂

I agree about the performance hit. If you really do need the counts and performance is an issue for you, I suggest that you consider doing the inserts and updates separately and using the old-fashioned @@RowCount method.

Use a transaction to keep things atomic, if your needs require it. Performance will still be better – in my experience.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Quick thought, consider changing the hashing part of the statement as it will not produce unique hash values for every unique combination of input values.

😎

More details here.