SQL Server Performance Forum – Threads Archive

about copying large table

Hi everybody, I have to copy a large table from one SQL Server to another. The table is about 2 million records, and it is more then 200M after exported to text file. I used SELECT INTO to copy the table, it costs about 2 mins. But this is a real-time table, if users are inputting data when I copying this table, the copy operation will be very slow. I need to improve the performance of this task. Anybody has good suggestion? Thanks a lot! Best Regards,
Fan

Is it a one time operation or something that needs to be done regularly? Do you also have follow updates/deleted that occured during the operation?

One idea is to break the move up into batches..
Is there an incrementing primary key on the table you can use ? Maybe you can then do a while loop, selecting top N where field > lastfieldcopied this might yield more time to other users processes in between, but will probably take longer to complete. Should also be easier going on the transaction log

To bambola: It is a task that need to be done regularly and there is not just one table need to be copied. I will never need to update delete the records in the table, I only copy it for backup; The users only input data but not update/delete during the operation. To Chappy: There is no an incrementing primary key in the table. I have tried using "BCP", but it need to copy the record to one text file then copy the file to another server/database. It wastes time for disk writing and the performance is worse than SELECT INTO. Thanks a lot!!
Regards,
Fan

How about the h/w capability and other applications sharing the machine resources? A fast way to transfer data between 2 SQL Servers is via DTS. You can specify the ‘fast ‘load’ and ‘table lock options to maximize performance if the target table has no indexes. Also, run the package directly on the source or destination server rather than an intermediate box.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

How to implement DTS in Store Procedure? Would you please give me a sample?
Thanks a lot! Regards,
Fan

Your choice, either xp_cmdshell with dtsrun, or the sp_OAmethod family to do it using automation.
And take help from this linkhttp://www.databasejournal.com/features/mssql/article.php/1459181 about DTS in and out. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks satya! You really mean it. Regards,
Fan

Wait a minute. The idea was toc copy the data from the table to another table. Whay would you need to copy the entire data everytime. Why not copy the changed data only? Changed data can be coopied in two modes, batch and online. In batch mode, after a certain duration check if some records have changes since the last sync, if yes, copy the same using simple Insert Into statement. In the online mode, write an update / insert trigger on the table to copy the changed data to new table. Am I missing something? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>