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

Question

I have a table with 3 4 million rows  I want to copy this whole data into another table   I am performing this task using the below query   select    into new items  from productDB dbo items   I need to know the best possible way to do this task

User · Answer

I have been working with our DBA to copy an audit table with 240M rows to another database.

Using a simple select/insert created a huge tempdb file.

Using a the Import/Export wizard worked but copied 8M rows in 10min

Creating a custom SSIS package and adjusting settings copied 30M rows in 10Min

The SSIS package turned out to be the fastest and most efficent for our purposes

Earl

User · Answer

If your focus is Archiving  DW  and are dealing with VLDB with 100  partitioned tables and you want to isolate most of these resource intensive work on a non production server  OLTP  here is a suggestion  OLTP -  DW   1  Use backup   Restore to get the data onto the archive server  so now  on Archive or DW you will have Stage and Target database  2  Stage database  Use partition switch to move data to corresponding stage table 3  Use SSIS to transfer data from staged database to target database for each staged table on both sides  4  Target database  Use partition switch on target database to move data from stage to base table  Hope this helps

User · Answer

Here s another way of transferring large tables  I ve just transferred 105 million rows between two servers using this  Quite quick too    Right-click on the database and choose Tasks Export Data   A wizard will take you through the steps but you choosing your SQL server client as the data source and target will allow you to select the database and table s  you wish to transfer    For more information  see https   www mssqltips com sqlservertutorial 202 simple-way-to-export-data-from-sql-server

User · Answer

select   into new items from productDB dbo items    That pretty much is it  THis is the most efficient way to do it

User · Answer

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  lt   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

User · Answer

Simple Insert Select sp s work great until the row count exceeds 1 mil  I ve watched tempdb file explode trying to insert select 20 mil   rows  The simplest solution is SSIS setting the batch row size buffer to 5000 and commit size buffer to 1000

User · Answer

If it s a 1 time import  the Import Export utility in SSMS will probably work the easiest and fastest   SSIS also seems to work better for importing large data sets than a straight INSERT     BULK INSERT or BCP can also be used to import large record sets     Another option would be to temporarily remove all indexes and constraints on the table you re importing into and add them back once the import process completes   A straight INSERT that previously failed might work in those cases   If you re dealing with timeouts or locking blocking issues when going directly from one database to another  you might consider going from one db into TEMPDB and then going from TEMPDB into the other database as it minimizes the effects of locking and blocking processes on either side   TempDB won t block or lock the source and it won t hold up the destination   Those are a few options to try   -Eric Isaacs

User · Answer

If you are copying into a new table  the quickest way is probably what you have in your question  unless your rows are very large   If your rows are very large  you may want to use the bulk insert functions in SQL Server   I think you can call them from C    Or you can first download that data into a text file  then bulk-copy  bcp  it   This has the additional benefit of allowing you to ignore keys  indexes etc   Also try the Import Export utility that comes with the SQL Management Studio  not sure whether it will be as fast as a straight bulk-copy  but it should allow you to skip the intermediate step of writing out as a flat file  and just copy directly table-to-table  which might be a bit faster than your SELECT INTO statement

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to copying