CODEWITHSUNDEEP
sqlsql-servert-sql
codeSeven
codeSeven

Reputation: 469

SQL MERGE - when not matched insert by using select table

Is it possible, if not exists then I will insert my data using select? I'm using user-defined table to which is the @ProcessItem variable to insert all the non-existing. 

MERGE INTO EmpClearancePendingItems1 a
USING @ProcessItem b ON a.OnProcessItem = b.OnProcessItem
                     AND a.TranNo = @TranNo

WHEN MATCHED THEN
    UPDATE 
        SET a.Amount = b.Amount,
            a.Remarks = b.Remarks,
            a.IfOthers = b.IfOthers

WHEN NOT MATCHED BY TARGET THEN
    INSERT (UserID, OnProcessItem, Amount, Remarks, TranNo, IfOthers)
    VALUES (SELECT @ResignEmp, OnProcessItem, Amount, Remarks, @TranNo, IfOthers
            FROM @ProcessItem);

Upvotes: 1

Views: 4067

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

Merge operation is acting on row by row basis and as per MSDN documentation, only VALUES clause is supported for WHEN NOT MATCHED. You don't need SELECT clause and also, syntax does not support it also.

[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  


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

Below would suffice:

INSERT (UserID, OnProcessItem, Amount, Remarks, TranNo, IfOthers)
    VALUES (@ResignEmp, OnProcessItem, Amount, Remarks, @TranNo, IfOthers)

Upvotes: 1

marc_s
marc_s

Reputation: 754518

Since most of your values are columns from the source table anyway - I don't see any reason why you would need a SELECT in the INSERT - just try this:

WHEN NOT MATCHED BY TARGET THEN
    INSERT (UserID, OnProcessItem, Amount, Remarks, TranNo, IfOthers)
    VALUES (@ResignEmp, b.OnProcessItem, b.Amount, b.Remarks, @TranNo, b.IfOthers);

Upvotes: 3

Related Questions