question

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 asked techresearch7777777-7743 commented

Fastest way to copy a table?

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.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @techresearch7777777-7743,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

Thanks again for all of the replies.

I did a test with select * into and was still running after 8 hours.

I did another test using the Import/Export Wizard (saved it as a SSIS package) and ran as a SQL Agent job and it completed around 6 hours. (Does this method use some type of Bulk operation under the hood which I would think is faster with minimal/to no logging?)

0 Votes 0 ·
TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered techresearch7777777-7743 commented

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.. :-)

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Tibor.

So is it correct to say that a select * into does no or minimal logging (the Transaction Log) ?

0 Votes 0 ·

All operations are logged. SELECT INTO is a minimally logged operation, provided that the recovery model for the database isn't full.

0 Votes 0 ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered TiborKaraszi commented

Hi @techresearch7777777-7743,

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Make sure you talk to the DBA before changing recovery model. Changing recovery model affects backup and restore.

0 Votes 0 ·

Thanks for pointing this out.

Change the recovery model to Full recovery model again after finishing copying data.

0 Votes 0 ·

... and talk to your DBA, since you now have a point in time where you have limitations for where you can do point in time restore.

0 Votes 0 ·