Sql Merge- How to Insert row from source table after deleting original from Target when matched?

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

);

I need to change this, so that when match is found on the Ticket Number, instead up just updating it, I need to A.)replace the current row in the Target table by deleting it, then B.)inserting the corresponding Row from the source table.

I currently have

MERGE INTO Ticket_REPORT T1
  USING @New_Tickets T2
  ON T1.Ticket_NO=T2.Ticket_NO
  WHEN MATCHED THEN DELETE
//Now I need to replace what I deleted with the row from the source table

Which will delete the row from the Target Table. Now I want to Insert the corresponding Row from the Source Table. I am having trouble trying to do multiple things inside the WHEN MATCHED clause. Does anyone know how I can accomplish this?

*Side note: When matched, I could Insert the row from the Source, but then I how would I delete the original?

What's the point of the merge then? Just delete everything in ticket_Report when in new_ticket then insert everything from new_ticket. — xQbert, Dec 22 '15 at 16:49
@xQbert I asked that same question, but this is how the boss wants it done. — Reeggiie, Dec 22 '15 at 16:52
If I had to guess, someone doesn't understand what a merge does. Especially if the tables only contain 5 columns. if there are more, then I might understand the delete first. — xQbert, Dec 22 '15 at 16:57
@xQbert There are many more columns, I just reduced the amount I used in this post for the sake of simplicity. — Reeggiie, Dec 22 '15 at 17:03

score 0 · Accepted Answer · answered Dec 22 '15 at 16:55

0

Strictly solution For your boss

First put the matched records into one temp table
Next use Merge query to delete the matched records from target table and Insert the unmatched records
Finally insert the records from temp table to target table

Try something like this

select * into #temp 
from  @New_Tickets T2 
where exists(select 1 
             from Ticket_REPORT T1
             where T1.Ticket_NO=T2.Ticket_NO)


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

insert into Ticket_REPORT (col1,col2,..)
select col1,col2,..
from #temp

Note :

What has to be done is delete the matched records from target table and insert all then records from source table to target table

answered Dec 22 '15 at 16:55

Pரதீப்

91,748
19
131
172

This seems perfect. Can you explain what the '1' means in "Select 1 .."? – Reeggiie Dec 22 '15 at 17:00
Just a value to check the existence of a record. It can be anything `*`, `columnname`, etc.. – Pரதீப் Dec 22 '15 at 17:02
Am I able to use an existing table as the temp table? Or do I need to literately put '#temp'? Because I am getting an error when I try to Select * into a preexisting table in my database. – Reeggiie Dec 22 '15 at 17:16
@Reeggiie - Its upto you, but `INTO` clause will create a new table – Pரதீப் Dec 22 '15 at 17:18
Thanks this seems to work. I dont have much experience with temp tables, will the temp table be reset everytime I run this procedure? – Reeggiie Dec 22 '15 at 17:40
@reeggiie temp tables are only in scope for the duration of the session. More [here](http://stackoverflow.com/questions/3331807/temporary-table-scope) or: [here](http://stackoverflow.com/questions/18614344/scope-of-temporary-tables-in-sql-server-2012) and from [MSFT directly](https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx) – xQbert Dec 22 '15 at 18:18

score 0 · Answer 2 · answered Dec 22 '15 at 17:12

One approach is to subquery your MERGE statement. You can filter the output based on the action (INSERT, UPDATE or DELETE). The filtered records can then be INSERTED, UPDATED or DELETED.

This is a common technique for loading slowly changing dimensions into a data warehouse.

My example uses the follow temp tables:

SETUP

/* Create and populate sample tables.
 */
CREATE TABLE #NewTicket
    (
        Id INT
    )
;

CREATE TABLE #TicketReport
    (
        Id INT
    )
;

INSERT INTO #NewTicket
    (
        Id
    )
VALUES
    (1),
    (2),
    (3)
;

INSERT INTO #TicketReport
    (
        Id
    )
VALUES
    (3),
    (4),
    (5)
;

In the example 1, 2 and 3 appear in new ticket. 3, 4 and 5 appear in ticket report. 3 is deleted by the MERGE statement and INSERTED by the outer query.

EXAMPLE

/* Filter the results from the sub query 
 * for deleted records.
 * These are then appended in the main
 * outer query.
 */
INSERT INTO #TicketReport
    (
        Id
    )
SELECT
    Id
FROM
    (
        /* MERGE statments can be used as a sub query.
         * You'll need the OUTPUT clause for this to work.
         * The column $action describes what happened to each record.
         */
        MERGE
            #TicketReport AS t
                USING #NewTicket AS s        ON s.Id = t.Id
        WHEN MATCHED THEN
            DELETE
        WHEN NOT MATCHED BY TARGET THEN
            INSERT
                (
                    Id
                )
            VALUES
                (
                    Id
                )
        OUTPUT 
            $action,
            s.*
    ) AS r
WHERE
    [$Action] = 'DELETE'
;

/* View the final result.
 */
SELECT
    *
FROM
    #TicketReport
ORDER BY
    Id
;

Sql Merge- How to Insert row from source table after deleting original from Target when matched?

2 Answers2