sqlsql-servermergesql-server-2008-r2

Merge insert with select statement


This works for me

MERGE Table1 AS tgt
USING
(
    SELECT
        TOP 1
        *
    FROM
        Table2,
        (
            SELECT
                itmid
            FROM
                Table3
            WHERE
                id = @id
        ) as a
    WHERE
        id = @id
) AS src
ON ( tgt.id = src.id )
WHEN MATCHED THEN
    UPDATE SET qty = qty + @qty
WHEN NOT MATCHED THEN
    INSERT itmid
    VALUES itmid;

But when i change like this, its not working, showing error near last select

MERGE Table1 AS tgt
USING
(
    SELECT
        TOP 1
        *
    FROM
        Table2
    WHERE
        id = @id
) AS src
ON ( tgt.id = src.id )
WHEN MATCHED THEN
    UPDATE SET qty = qty + @qty
WHEN NOT MATCHED THEN
    INSERT itmid
    SELECT itmid FROM Table3 WHERE id = @id;

Solution

  • According to MSDN docs

    <merge_not_matched>::=
    {
        INSERT [ ( column_list ) ] 
            { VALUES ( values_list )
            | DEFAULT VALUES }
    }
    

    The syntax with INSERT from SELECT like:

      WHEN NOT MATCHED THEN
                INSERT itmid SELECT itmid FROM Table3 WHERE id=@id;
    

    Is not allowed!

    I would try to solve your problem doing another merge with Table3.