0

I am trying to move close 88101840 rows from one table to another. To handle log growth on the server looking at doing 10000 rows at time. Please have a look at the query below. Just want to find if this way is good or would there be a better way to do it.

DECLARE @Archive_Rows INT;

SET @Archive_Rows = 1;

WHILE (@Archive_Rows > 0)
  BEGIN
    DELETE TOP (10000) Audit
    OUTPUT deleted.*
    INTO AuditArchive
    WHERE EVENTYEAR = '2016';

    SET @Archive_Rows = @@ROWCOUNT;
  END
  • 2
    Read this helpful article on your question:michaeljswart.com/2014/09/take-care-when-scripting-batches – Denis Rubashkin Nov 30 '18 at 7:07
  • Is EVENTYEAR indexed? – Jonathan Fite Nov 30 '18 at 13:21
  • If your AuditArchive is empty now and you try to move more data than should remain, maybe it is simpler to copy just the data that you plan to preserve to a new table, rename it as Audit,and delete it from current Audet table – sepupic Nov 30 '18 at 13:57
  • Thanks you all . Yes. Eventyear is indexed. Ended up going through with this approach. Was much faster and log growth was minimal. – Halt_And_Catch_Fire Dec 3 '18 at 4:24
1

Method 1,

DECLARE @TopSize INT = 10000
DECLARE @BatchSize INT = 10000
DECLARE @MaxLimit INT = 1
DECLARE @RowCount INT = 0

BEGIN TRY
    WHILE (@TopSize <= @MaxLimit)
    BEGIN
        DELETE TOP ((@TopSize) Audit
    OUTPUT deleted.*
    INTO AuditArchive
    WHERE EVENTYEAR = '2016';

        SET @RowCount = @@RowCount

        --PRINT @TopSize
        IF (
                @RowCount = 0
                OR @RowCount IS NULL
                )
            BREAK;
        ELSE
            SET @TopSize = @TopSize + @BatchSize
    END
END TRY

BEGIN CATCH
    --catch error
END CATCH

where EVENTYEAR is index column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.