Peter Bannister (12/24/2012)
Hi,a while ago I had to create an environment copy for a large database at a client site.
I tried several methods to copy the large tables, but the one which provided the fastest copy was to drop the table and indexes - "select into" the add back all the indexes and stats.
A couple of caveats:
1) this was a copy to development!!!
2) The logging method was simple
3) I generated a script to run through the indexes and recreate them
I hope this gives you food for thought.
CHeers
Peter
The reason why SELECT INTO <new table> is faster than INSERT INTO <existing table> is because SELECT INTO doesn't transaction log the data pages being inserted. In the event of rollback, SQL Server simply has to discard the table. However, when inserting into an existing table, SQL Server has to keep track of what pages are being modified to support a rollback.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho