locked
Merge statement insert and then delete RRS feed

  • Question

  • I was wondering how this would work. I want any records returned from the query to insert those records into one table and then delete them from the original table. I can't figure out how to do it with a merge statement.

    Alan

    Thursday, July 24, 2014 1:55 AM

Answers

  • If I understand what you want to do, you cannot do it with a single MERGE command.  A merge command has a source (table or query) and a target table.  The merge command is only allowed to make changes to the target.  It cannot change the source.

    One way to do what you want would be to use the output clause to save the primary key of any row in the source that was inserted into the target into a temporary table, and then use the temp table to delete those rows from the source.  For example

    Use tempdb go Create Table #Target(Id int primary key, OtherData varchar(20)); Insert #Target(Id, OtherData) Values (1, 'Row 1'), (3, 'Row 3'); Create Table #Source(Id int primary key, OtherData varchar(20)); Insert #Source(Id, OtherData) Values (2, 'New Row 2'), (3, 'New Row 3'); Begin Try Create Table #Output(ActionTaken char(6), Id int primary key); Begin Transaction Merge #Target t Using #Source s On t.Id = s.Id When Not Matched By Target Then Insert(Id, OtherData) Values(s.Id, s.OtherData) Output $action, s.Id Into #Output(ActionTaken, Id); Delete t From #Source t Inner Join #Output o On t.Id = o.Id Where o.ActionTaken = 'INSERT'; Drop Table #Output;

    Commit; End Try Begin Catch Rollback End Catch; Select 'Target', * From #Target; Select 'Source', * From #Source; Drop Table #Target; Drop Table #Source;

    Tom


    • Edited by Tom Cooper Friday, July 25, 2014 2:56 AM Added missing commit
    • Proposed as answer by Satheesh Variath Friday, July 25, 2014 4:56 AM
    • Marked as answer by Elvis Long Wednesday, July 30, 2014 9:10 AM
    Friday, July 25, 2014 12:01 AM

All replies

  • If I understand what you want to do, you cannot do it with a single MERGE command.  A merge command has a source (table or query) and a target table.  The merge command is only allowed to make changes to the target.  It cannot change the source.

    One way to do what you want would be to use the output clause to save the primary key of any row in the source that was inserted into the target into a temporary table, and then use the temp table to delete those rows from the source.  For example

    Use tempdb go Create Table #Target(Id int primary key, OtherData varchar(20)); Insert #Target(Id, OtherData) Values (1, 'Row 1'), (3, 'Row 3'); Create Table #Source(Id int primary key, OtherData varchar(20)); Insert #Source(Id, OtherData) Values (2, 'New Row 2'), (3, 'New Row 3'); Begin Try Create Table #Output(ActionTaken char(6), Id int primary key); Begin Transaction Merge #Target t Using #Source s On t.Id = s.Id When Not Matched By Target Then Insert(Id, OtherData) Values(s.Id, s.OtherData) Output $action, s.Id Into #Output(ActionTaken, Id); Delete t From #Source t Inner Join #Output o On t.Id = o.Id Where o.ActionTaken = 'INSERT'; Drop Table #Output;

    Commit; End Try Begin Catch Rollback End Catch; Select 'Target', * From #Target; Select 'Source', * From #Source; Drop Table #Target; Drop Table #Source;

    Tom


    • Edited by Tom Cooper Friday, July 25, 2014 2:56 AM Added missing commit
    • Proposed as answer by Satheesh Variath Friday, July 25, 2014 4:56 AM
    • Marked as answer by Elvis Long Wednesday, July 30, 2014 9:10 AM
    Friday, July 25, 2014 12:01 AM
  • Rows are not records and that fundamental error tells us your mindset is still in punch cards.  You want to physically move a unit record (punch card) from one deck to another! 

    An SQL programmer would simply show a change in status of the row in question. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, July 25, 2014 12:55 AM