How to copy a huge table data into another table in SQL Server

by

I had the same problem, except I have a table with 2 billion rows, so the log file would grow to no end if I did this, even with the recovery model set to Bulk-Logging:

insert into newtable select * from oldtable

So I operate on blocks of data. This way, if the transfer is interupted, you just restart it. Also, you don’t need a log file as big as the table. You also seem to get less tempdb I/O, not sure why.

set identity_insert newtable on
DECLARE @StartID bigint, @LastID bigint, @EndID bigint
select @StartID = isNull(max(id),0) + 1
from newtable

select @LastID = max(ID)
from oldtable

while @StartID < @LastID
begin
    set @EndID = @StartID + 1000000

    insert into newtable (FIELDS,GO,HERE)
    select FIELDS,GO,HERE from oldtable (NOLOCK)
    where id BETWEEN @StartID AND @EndId

    set @StartID = @EndID + 1
end
set identity_insert newtable off
go

You might need to change how you deal with IDs, this works best if your table is clustered by ID.

More Related Contents:

  1. Insert an image in a column of a table that already has 5 columns
  2. Get top 1 row of each group
  3. Referring to a Column Alias in a WHERE Clause
  4. How to SELECT FROM stored procedure
  5. Convert Month Number to Month Name Function in SQL
  6. How to avoid the “divide by zero” error in SQL?
  7. SET versus SELECT when assigning variables?
  8. Generate a resultset of incrementing dates in TSQL
  9. Real life example, when to use OUTER / CROSS APPLY in SQL
  10. Getting result of dynamic SQL into a variable for sql-server
  11. How to get difference between two rows for a column field?
  12. Better techniques for trimming leading zeros in SQL Server?
  13. How to print VARCHAR(MAX) using Print Statement?
  14. SQL statement to select all rows from previous day
  15. What’s the best way to select the minimum value from several columns?
  16. Concatenate values based on ID
  17. Splitting delimited values in a SQL column into multiple rows
  18. varbinary to string on SQL Server
  19. How to insert a blob into a database using sql server management studio
  20. SQL GROUP BY CASE statement with aggregate function
  21. separate comma separated values and store in table in sql server
  22. Restrict an SQL Server connection to a specific IP address
  23. SQL Server 2005 ROW_NUMBER() without ORDER BY
  24. Can an INNER JOIN offer better performance than EXISTS
  25. SQL server identity column values start at 0 instead of 1
  26. How do I expand comma separated values into separate rows using SQL Server 2005?
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. SQL Sort Order with Null values last
  29. Insert all values of a table into another table in SQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment