All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
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