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

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.

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.

StackExchange Database Administrators Q#310791, answer score: 14