Home Forums SQL Server 2005 Administering Copy Between 2 and 3 billion rows from a heap table with no key to a new partioned table. RE: Copy Between 2 and 3 billion rows from a heap table with no key to a new partioned table.

  • I usually handle large data moves in batches by setting up a loop and copying a set amount. Since these are during production I put in a delay to prevent hogging all the cpu. I don't know if this is practical for your situation.

    InsertMore:

    WAITFOR DELAY '00:00:05' -- 5 second delay allow other process some CPU

    INSERT top (100000) INTO NewTable

    SELECT columns

    FROM OldTable

    where id between @ID_First and @ID_Last

    and ID not in (select ID from NewTable) -- Not already inserted

    if @@rowcount > 0 goto InsertMore