locked
how to move large data RRS feed

  • Question

  • We have one table with 100 million data. We would like move some of data(around 50 - 70 million rows) to archive table. we run the following query and got tempdb/transaction log full issue.  Is that possible we can do this as batch at one time? for example, move 500K and delete them.

     

    INSERT INTO dbo.Archive

    (

    Column1... ColumnX

    )

    SELECT Column1...ColumnX

    FROM dbo.Table1

    WHERE ....

     

    DELETE TABLE1

    WHERE ....

    Tuesday, February 15, 2011 5:04 PM

Answers

  • Yes, something like this:

     

    declare @Cnt int, @BatchSize int
    
    set @Cnt = 1
    
    set @BatchSize = 50000 -- 50K
    
    while @Cnt > 0
    
      begin
    
        delete top (@BatchSize)  from MyTable
    
        OUTPUT Deleted.Col1, Deleted.Col2, etc. into dbo.Archive
    
       WHERE ...
    
       set @Cnt = @@ROWCOUNT
    
    end
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by TravelMan Tuesday, February 15, 2011 5:48 PM
    Tuesday, February 15, 2011 5:18 PM

All replies

  • Yes, something like this:

     

    declare @Cnt int, @BatchSize int
    
    set @Cnt = 1
    
    set @BatchSize = 50000 -- 50K
    
    while @Cnt > 0
    
      begin
    
        delete top (@BatchSize)  from MyTable
    
        OUTPUT Deleted.Col1, Deleted.Col2, etc. into dbo.Archive
    
       WHERE ...
    
       set @Cnt = @@ROWCOUNT
    
    end
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by TravelMan Tuesday, February 15, 2011 5:48 PM
    Tuesday, February 15, 2011 5:18 PM
  • Thanks your help. 
    Tuesday, February 15, 2011 5:49 PM