Post reply

Udate not happening using merge statement

  • February 11, 2015 at 10:37 pm

    #315265

    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

    #1777381

    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