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: https://www.erikdarlingdata.com/sql-server/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 https://www.erikdarlingdata.com/sql-server/mind-your-output-targets/ , the plan will be forced serial.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2