CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-updatesql-delete
Deepak
Deepak

Reputation: 97

SQL : Delete And Insert In One Stored Procedure

I want delete query execute only once in the beginning. After that execution of insert statement, until the all data successfully saved in DB.

Upvotes: 0

Views: 2345

Answers (1)

Gaurav Moolani
Gaurav Moolani

Reputation: 362

Try using MERGE statement

-- MERGE statement with the join conditions specified correctly.

USE tempdb;
GO
BEGIN TRAN;
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)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

Upvotes: 1

Related Questions