Fastest way to copy a table?

Question

Hello, having a large table say over 100 million rows to backup only...would it be faster to do a select " into or use like the Import/Export Wizard within Management Studio (believe it uses like some type of Bulk insert operation under the hood).

Thanks in advance.

Answer 1

Yes, SSIS does by default use the Bulk Loading functionality in SQL Server, which does minimal logging. SELECT INTO can also do minimal logging, however. Minimal logging requires that your recovery model for the database isn't FULL (regardless of whether it is bulk loading or SELECT INTO).

It is difficult to say why SSIS was faster than SELECT INTO. One would want to play with it and try various things - without that we are left with speculation. One aspect is that the transaction log can be emptied every now and then with SSIS, depending on what batchsize/transactionsize you end up with - and that can somehow affect performance. But, again, too many aspects to do any precise reasoning.. :-)

Answer 2

I usually use select * into statement as It seems faster method to me. Else there is one option give here which transfers in block

https://stackoverflow.com/questions/24898427/fastest-way-to-copy-sql-table

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 3

Hi @techresearch7777777 ,

Suggest you using select *into as VaibhavChaudhari mentioned.

In addition, suggest you change your database recovery mode to BULK-LOGGED recovery model. Under the “BULK_LOGGED” recovery, for bulk operations the transactions are not fully logged so this may help in the execution time.

Refer to the blog Optimize Moving SQL Server Data From One Table to Another Table.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.