February 11, 2015 at 10:37 pm
Hi ,
Even rowchecksum is different in target from source then also update is not happening
alter Procedure SP_Archive_using_merge
AS
BEGIN
SET NOCOUNT ON
Declare @Source_RowCount int
Declare @New_RowCount int
DECLARE @TimeIn SMALLDATETIME
DECLARE @LatestVersion INT
select @TimeIn=getdate()
Select @LatestVersion=1
MERGE Archive.dbo.ArchiveBBxCemxr AS stm
USING
(
SELECT a.*,cast(SUBSTRING(a.Col001,1,10) as varchar(100)) BBxKey,
HashBytes('MD5', CAST(CHECKSUM(a.Col001,a.Col002,a.Col003,a.Col004,a.Col005,a.Col006,a.Col007) AS VARCHAR(MAX))) RowChecksum,
b.BBxKey as Archive_BBxKey, b.RowChecksum as Archive_RowChecksum
FROM dbo.ImportBBxCemxr a LEFT OUTER JOIN Archive.dbo.ArchiveBBxCemxr b
ON a.Col001 = b.BBxKey
Where (b.LatestVersion = 1 OR b.LatestVersion IS NULL) AND a.Col001 IS NOT NULL --AND A.Col001='0000010806'
) AS sd
ON sd.Col001 = stm.BBxKey --and sd.RowChecksum = stm.RowChecksum
WHEN MATCHED AND (stm.BBxKey = sd.Archive_BBxKey or stm.RowChecksum <> sd.Archive_RowChecksum) THEN
UPDATE SET
stm.TimeIn = @TimeIn,
stm.BBXKey=sd.BBXKey,
stm.RowChecksum=sd.RowChecksum,
stm.Col001=sd.Col001,
stm.Col002=sd.Col002,
stm.Col003=sd.Col003,
stm.Col004=sd.Col004,
stm.Col005=sd.Col005,
stm.Col006=sd.Col006,
stm.Col007=sd.Col007,
stm.LatestVersion=@LatestVersion
WHEN NOT MATCHED and (sd.Archive_BBxKey is null) THEN
Insert (TimeIn,BBXKey,RowChecksum,Col001,Col002,Col003,Col004,Col005,Col006,Col007,LatestVersion)
values(getdate(),sd.BBXKey,sd.RowChecksum,sd.Col001,sd.Col002,sd.Col003,sd.Col004,sd.Col005,sd.Col006,sd.Col007,@LatestVersion);
select @New_RowCount=@@ROWCOUNT
--lect @Source_RowCount ,@New_RowCount
select @New_RowCount
end
February 12, 2015 at 5:39 pm
A thought. I assume that you know of a row in the target that matches a row in the source. It matches on the key and on the checksum. What happens when you query the target on the key and query the full source query on the key. Does a row with that key show up in both queries?
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply