Is there a way to Delete inserted data, or vice versa?

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