CODEWITHSUNDEEP
sqlsql-server-2008relational-database
Jack
Jack

Reputation: 1493

Having MERGE INTO do 2 things when Not Matched?

I am using the MERGE INTO statement to work with some tables, and I've a simple question. How can I make a Matched/Not Matched Statement do 2 things? For example, I have this and it works: 

MERGE INTO VMFG.dbo._AR_Mill1ActiveSchedule t --target
    USING #temp  --source
      On t.Base_ID = #temp.WORKORDER_BASE_ID AND t.Lot_ID = #temp.WORKORDER_Lot_ID
WHEN MATCHED AND #temp.rollstatus = 'R' THEN
    UPDATE
     SET t.sawStatus = #temp.sawstatus, t.rollStatus = #temp.rollstatus
WHEN NOT MATCHED BY TARGET THEN
    INSERT (BASE_ID, LOT_ID, sawStatus, rollstatus, preheatcheck) VALUES (#temp.WorkOrder_Base_ID, #temp.WorkOrder_Lot_ID, #temp.sawStatus, #temp.rollStatus, 'False')
WHEN NOT MATCHED BY SOURCE AND t.SawStatus = 'C' THEN
    Delete ;

Drop Table #temp

Now, I want to accomplish something like this:

MERGE INTO VMFG.dbo._AR_Mill1ActiveSchedule t --target
    USING #temp  --source
      On t.Base_ID = #temp.WORKORDER_BASE_ID AND t.Lot_ID = #temp.WORKORDER_Lot_ID
WHEN MATCHED AND #temp.rollstatus = 'R' THEN
    UPDATE
     SET t.sawStatus = #temp.sawstatus, t.rollStatus = #temp.rollstatus
WHEN NOT MATCHED BY TARGET THEN
    INSERT (BASE_ID, LOT_ID, sawStatus, rollstatus, preheatcheck) VALUES (#temp.WorkOrder_Base_ID, #temp.WorkOrder_Lot_ID, #temp.sawStatus, #temp.rollStatus, 'False')
WHEN NOT MATCHED BY SOURCE AND t.SawStatus = 'C' THEN
    INSERT INTO _AR_Mill1RemovedWO (BASE_ID, LOT_ID) VALUES (#temp.WorkOrder_Base_ID, #temp.WorkOrder_Lot_ID)
    Delete ;

Drop Table #temp

However, when I run this I get the error "Incorrect syntax near the keyword 'INTO'".

I've tried using the OUTPUT clause but I couldn't get it to work correctly, and would rather use an insert statement anyways if possible.

Thank you :)

EDIT: The Output clause would be awesome to use, but I'll explain why it hasn't worked. When I used it, i.e.:

....
WHEN NOT MATCHED BY SOURCE AND t.SawStatus = 'C' THEN
--INSERT INTO _AR_Mill1RemovedWO (BASE_ID, LOT_ID) VALUES (#temp.WorkOrder_Base_ID, #temp.WorkOrder_Lot_ID)
Delete 
OUTPUT deleted.Base_ID, deleted.Lot_ID INTO VMFG.dbo._AR_Mill1RemovedWO
    ;

Drop Table #temp

I run in to a problem. It adds rows to my table that weren't actually deleted. It's like it's just adding everything, regardless of being deleted. Is there a reason for this?

Upvotes: 1

Views: 1082

Answers (1)

usr
usr

Reputation: 171188

This is impossible to do with a single MERGE statement. Use the OUTPUT clause to direct information about the writes executed into a table variable. Execute a second statement such as INSERT to write to the second table.

Upvotes: 1

Related Questions