Delete using MERGE

Question

Delete using MERGE

oradbguru

Hall of Fame

Points: 3126
More actions
November 11, 2015 at 9:25 am

#322497

Hello, I have a table that I need to delete from but only if the rows I am deleting exist in a backup table. I know I can use a MERGE statement for this. Can anyone please provide an example? or suggest a better way? Using an IN or NOT EXISTS is not a solution as both table are very large and the backup table will be using a linked server.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

EXISTS should be better than a MERGE which might need an expensive sort operation.

How much are you deleting and how much are you keeping in the table? Maybe that would change the approach. You could also delete in batches.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

oradbguru Hall of Fame Points: 3126 More actions · Answer 2

The merge would be based on a single unique id. I am deleting millions of records from a table that has 150+ million records in it. This will run daily in an effort to archive data.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

oradbguru (11/11/2015)
The merge would be based on a single unique id. I am deleting millions of records from a table that has 150+ million records in it. This will run daily in an effort to archive data.

Have you looked into using DELETE with an output clause, possibly Composable DML?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

oradbguru Hall of Fame Points: 3126 More actions · Answer 4

oradbguru

Hall of Fame

Points: 3126

November 11, 2015 at 11:18 am

#1839183

No I have not. Do you have examples?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Sure. Try this:

CREATE TABLE #Audit2 (RowID INT IDENTITY(1,1), CustomerID INT, [Action] CHAR(1), oldValue INT, newValue INT NULL) ;

CREATE TABLE #Trans2 (CustomerID INT, Value INT);

INSERT #Trans2 VALUES (1,100), (2,200), (3,300), (4,400), (5,500), (6,600);

CREATE CLUSTERED INDEX cx_CustomerID ON #Trans2 (CustomerID)

;WITH RowsToDelete AS (

SELECT TOP(50000) -- rowsaffected throttle

CustomerID, Value

FROM #Trans2

WHERE CustomerID IN (1,3,5)

ORDER BY CustomerID)

INSERT #Audit2 (CustomerID, [Action], oldValue, newValue)

SELECT CustomerID, 'D', Value, NULL

FROM (

DELETE t

OUTPUT deleted.CustomerID,

deleted.Value

FROM RowsToDelete t

) d

WHERE CustomerID <> 1

SELECT * FROM #Audit2

SELECT * FROM #Trans2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden