The question:
I’m trying to get Data inserted and then Deleted in the same query.
I’m trying to delete it in chunks to not cause LOG issues:
DECLARE @BatchSize INT
SET @BatchSize = 100000
WHILE @BatchSize <> 0
BEGIN
DELETE (@BatchSize) TABLE1
WHERE LogType = 'LOGTYPE'
AND TABLE1.Id NOT IN (SELECT Id FROM TABLE2)
AND TABLE1.Id IN (SELECT Id FROM DifferentDB..TABLE3)
SET @BatchSize = @@rowcount
END
But this same query , I would like to insert data in a secondary DB first, and then delete.
Is it possible to achieve with no triggers? (inserted, deleted…)
I’m also open for a better delete in chunks approach, I just got that one by memory.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
This is pretty easy to do. I’ve written about it before here: Simplifying Archival Processes
A sample query would look sort of like this:
INSERT
dbo.Votes_Archive
(
Id,
PostId,
UserId,
BountyAmount,
VoteTypeId,
CreationDate
)
SELECT
v.*
FROM
(
DELETE v
OUTPUT
Deleted.Id,
Deleted.PostId,
Deleted.UserId,
Deleted.BountyAmount,
Deleted.VoteTypeId,
Deleted.CreationDate
FROM dbo.Votes AS v
WHERE v.UserId = 190597
) AS v;
You do need to be careful with OUTPUT, because if your target is a client application or table variable, the plan will be forced serial.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0