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

by

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

Leave a Comment