SQL MERGE INTO - How to delete then insert matched/existing data?

Question

I currently have a stored procedure that compares my target table (Ticket_Report) to my data source table (New_Tickets).

I am using a MERGE INTO statement to compare these two. When it finds a match between the two tables, it updates the current row in the target table with the corresponding info from the source table. If it dosent find a match, it inserts that data from the source table into the target table.

 MERGE INTO Ticket_REPORT T1
  USING @New_Tickets T2
  ON T1.TICKET_NO=T2.TICKET_NO
  WHEN MATCHED THEN
  UPDATE SET 
      T1.TICKET_NO = T2.TICKET_NO, 
      T1.ASSIGNED_GROUP = T2.ASSIGNED_GROUP, 
      T1.ASSIGNEE = T2.ASSIGNEE, 
      T1.FNAME = T2.FNAME, 
      T1.LNAME = T2.LNAME
WHEN NOT MATCHED THEN
  INSERT VALUES(
      T2.TICKET_NO, 
      T2.ASSIGNED_GROUP, 
      T2.ASSIGNEE, 
      T2.FNAME, 
      T2.LNAME
 );

What I need to do is, when I find a MATCH, instead of just updating the row, I need to delete that row, and re-insert it into the target table. Can anyone show me how to both DELETE and INSERT one after the other whenever I find a MATCH?

Answer 1

Here is an example:

DECLARE @t1 TABLE(id INT IDENTITY, col1 INT)
DECLARE @t2 TABLE(id INT IDENTITY, col1 INT, old_col1 INT)

INSERT INTO @t1(col1) VALUES(5), (10), (15)
INSERT INTO @t2(col1) VALUES(7), (14), (21), (28)

MERGE INTO @t2 t2
USING @t1 t1 ON t1.id = t2.id
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES(t1.col1, NULL)
OUTPUT t1.col1, Deleted.col1 INTO @t2(col1, old_col1);

SELECT * FROM @t2
ORDER BY id

Output:

id  col1  old_col1
4   28    NULL
5   5     7
6   10    14
7   15    21

First 3 rows have same ids in both, so match part will delete those(7, 14, 21). 28 will remain. And you insert new values and keep old values in OUTPUT clause.