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.
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.
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