Post reply

MERGE is not inserting new records on NOT MATCHED

  • October 29, 2018 at 9:54 am

    #367491

    i Guys,

    Can anyone shed some light on why the code below is not working.  The update will work when the records are MATCHED but the insert is not doing anything.  To see the update working, change the colourid in the DEBUG to match the value in the insert statement.

          BEGIN TRY
            DROP TABLE #adr_test
        END TRY
        BEGIN CATCH
            -- nothing to drop
        END CATCH

        CREATE TABLE #adr_test
        (
            style VARCHAR(5)
            ,size_id INT
            ,colour_id INT
            ,cost MONEY
        
        )

        INSERT INTO #adr_test (style,size_id,colour_id,cost) values ('ADR01',100,101,99.99)
        SELECT * FROM #adr_test

        /*DEBUG*/
        DECLARE @style VARCHAR(5) = 'ADR01'
        DECLARE @sizeid INT = 100
        DECLARE @colourid INT = 999
        DECLARE @ctncost MONEY = 1.50
        /*END DEBUG*/

        MERGE #adr_test AS Tgt
        USING
        (
            SELECT
                            style
                            ,size_id
                            ,colour_id
                            ,cost
            FROM
                            #adr_test                            
            WHERE
                            style = @style
                        AND
                            size_id = @sizeid
                        AND
                            colour_id = @colourid
        ) AS Src ON Src.style = Tgt.style
        AND Src.size_id = Tgt.size_id
        AND Src.colour_id = Tgt.colour_id
        
        WHEN MATCHED /*AND Tgt.cost <> @ctncost */THEN
            UPDATE SET Tgt.cost = @ctncost
        
        WHEN NOT MATCHED THEN
            INSERT
            (
                style
                ,size_id
                ,colour_id
                ,cost
            )
            VALUES
            (
                @style
                ,@sizeid
                ,@colourid
                ,@ctncost
            );

            SELECT * FROM #adr_test

  • October 29, 2018 at 10:20 am

    #2011554

    Does this version work?
    BEGIN TRY
      DROP TABLE #adr_test;
    END TRY
    BEGIN CATCH
    -- nothing to drop
    END CATCH;

    CREATE TABLE #adr_test
    (
      style  VARCHAR(5)
    , size_id INT
    , colour_id INT
    , cost  MONEY
    );

    INSERT INTO #adr_test
    (
      style
    , size_id
    , colour_id
    , cost
    )
    VALUES
    (
      'ADR01', 100, 101, 99.99
    );

    SELECT *
    FROM #adr_test;

    /*DEBUG*/
    DECLARE @style VARCHAR(5) = 'ADR01';
    DECLARE @sizeid INT = 100;
    DECLARE @colourid INT = 999;
    DECLARE @ctncost MONEY = 1.50;

    /*END DEBUG*/
    MERGE #adr_test Tgt
    USING
    (
      SELECT
       style  = @style
      , size_id = @sizeid
      , colour_id = @colourid
      , cost  = @ctncost
    ) Src
    ON Src.style = Tgt.style
     AND Src.size_id = Tgt.size_id
     AND Src.colour_id = Tgt.colour_id
    WHEN MATCHED /*AND Tgt.cost <> @ctncost */ THEN
      UPDATE SET Tgt.cost = @ctncost
    WHEN NOT MATCHED THEN
      INSERT
      (
       style
      , size_id
      , colour_id
      , cost
      )
      VALUES
      (
       @style, @sizeid, @colourid, @ctncost
      );

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 2 posts - 1 through 1 (of 1 total)

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