CODEWITHSUNDEEP
sqlsql-servermergesql-server-2008-r2
user1618825
user1618825

Reputation:

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;

Upvotes: 12

Views: 75132

Answers (3)

Edward
Edward

Reputation: 772

You want to define your src table better at the top of the MERGE statment.

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;

Assuming based on above that Table3.itemid is the desired value.

You would want to define a src table for the MERGE that included itemid from Table3 as a part of the columns returned.

You should define some table variable or CTE for the MERGE statement that joins Table2 and Table3 to fit your needs.

One simple example based on what you've provided could look like below. But your specific needs may vary based on the data schema.

WITH source AS ( 
    SELECT TOP 1 t3.itmid, t2.qty, ... [you could use * here but its better to list them out]
    FROM Table2 AS t2 
    INNER JOIN Table3 AS t3
        ON    t3.id = t2.id 
    WHERE t3.id = @id
)
MERGE source AS src...
...
WHEN NOT MATCHED THEN
    INSERT (itmid)
    VALUES src.itmid
;

Upvotes: 0

mipe34
mipe34

Reputation: 5666

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.

Upvotes: 14

Nikshep
Nikshep

Reputation: 2115

do this 

 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;

Should work fine according to http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Sorry I interpreted it wrong and my original answer would stand which was 

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) values (SELECT top 1 itmid FROM Table3 WHERE id=@id);

As merge insert takes one insert at a time and it might fail for multiple inserts.

Upvotes: -5

Related Questions