SQL Server Performance Forum – Threads Archive

SELECT INTO vs DTS DataPump Task

I have data in a source table that needs to be transfered into a destination table on a nightly basis. Is it faster to use a SELECT INTO or to use the DTS DataPump? There is effectively no transformation logic or filtering of the source table that needs to be performed. Does any one have any metrics on this?

---

I don’t have a one to one application on this. But yeah we had an application to transfer old data from one database to another. The normal SELECT INTO was used to design the application and it took 3 days for the data transfer. (The amount of data was huge and business logic was complex). Using DTS, we were able to accomplish the task in less than 9 hours. So i would definately recommend using DTS. Gaurav

---

If this is a regular job to be executed then follow as Gaurav indicated. As you don’t have any constraint in filtering or performance for the data load.
Make sure to have regular backups for Tlog during this DTS task which will ensure with size of Tlog. HTH _________
Satya SKJ

---

Thanks Satya, I forgot to mention that… During DTS data transfer you will have large amounts of Transaction Log if you go by Default Settings. Taking regular Tlog backups will not ensure that your log size does not keep on growing as there may be an open transaction which will take long time to finish and hence prevent the log size from getting reduced. Also any open transaction at the end of transaction log also prevents the log size to be reduced. However I would like to add of you turn on the USE FASTLOAD option, the transaction log size is much less hoever you have to take backup as soon as the operation is over. HTH. Gaurav

---

Rather you can setup recovery model to SIMPLE during the DTS load. _________
Satya SKJ

---

]]>