Post reply

t-sql 2012 insert not working on merge statement

  • October 7, 2015 at 10:51 am

    #312072

    In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.

    Merge test.dbo.LockCombination AS LKC1

    USING

    (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber

    from

    [Inputtb] A

    JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type

    JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number

    and LKR.[schoolID] = 1213

    JOIN test.dbo.Lock LK ON LKR.lockID = LK.lockID

    JOIN test.dbo.LockCombination LKC ON LK.lockID = LKC.lockID

    and LKC.seq = 1 and A.schoolnumber = 1213

    ) AS LKC2 (lockID,seq,combo,schoolnumber)

    ON

    (

    LKC1.lockID = LKC2.lockID

    and LKC1.seq = 1 and LKC2.seq =1

    )

    WHEN MATCHED AND LKC2.schoolnumber = @SchoolNumber

    THEN UPDATE SET LKC1.combo = LKC2.combo

    WHEN NOT MATCHED

    THEN INSERT (lockID,seq,combo) VALUES(LKC2.lockID,1,LKC2.combo);

    Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?

  • October 7, 2015 at 11:30 am

    #1832075

    By "not working" do you mean that you get an error (if so please post the message) or that rows are not inserted when you think they should be?

    If the latter, that means your WHEN NOT MATCHED clause is not firing.

    Gerald Britton, Pluralsight courses

  • October 7, 2015 at 11:31 am

    #1832076

    Just realized, you have the same table as the target and part of the source. Merge doesn't work properly in that scenario

    Gerald Britton, Pluralsight courses

  • October 7, 2015 at 11:55 am

    #1832080

    How would you change the merge statement?

  • October 7, 2015 at 11:55 am

    #1832082

    How would you change the merge statement?

  • October 7, 2015 at 11:56 am

    #1832084

    There is no error. The rows are not inserted.

  • October 7, 2015 at 12:16 pm

    #1832093

    wendy elizabeth (10/7/2015)

    How would you change the merge statement?

    If left up to me, the answer is easy... I'd get rid of the MERGE statement and use a traditional "UPSERT".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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