CODEWITHSUNDEEP
sqlsql-server
Nick Mehrdad Babaki
Nick Mehrdad Babaki

Reputation: 12545

SQL MERGE, Deleting each item from source after update or insert

I have a temporary table which I use to insert many data into rapidly . Then I use a SQL merge to insert data from temp table into main table(Target Table). But I want to delete each record from source table as soon as inserted into target table. Something like the code below:

    MERGE Target AS T
    USING Source AS S
    ON (T.EmployeeID = S.EmployeeID) 
    WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
        THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
        --THEN DELETE INSERTED ITEM------------------------------
    WHEN MATCHED 
        THEN UPDATE SET T.EmployeeName = S.EmployeeName
    WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
        THEN DELETE

Upvotes: 0

Views: 2024

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

I want to delete each record from source table as soon as inserted into target table.

In the RDBMS for most cases you can rephrase this as

I want to delete records from source table in the same transaction with INSERT/UPDATE

so I suggest is approach:

BEGIN TRANSACTION

MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
    --THEN DELETE INSERTED ITEM------------------------------
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE
OUTPUT inserted.EmployeeID INTO #TempTable

DELETE FROM Source
WHERE EmployeeID IN (SELECT EmployeeID FROM #TempTable)

COMMIT TRANSACTION

Upvotes: 1

James Z
James Z

Reputation: 12317

Like the documentation says, merge "Performs insert, update, or delete operations on a target table based on the results of a join with a source table".

Can't you just add a delete statement after the merge to delete rows from the original table that exists in the temp table?

Upvotes: 1

Related Questions