SQL Server: How to copy a very large table?
This blog is an extension to my earlier post on how to copy a table. This post is specific to the situation where you have to copy huge amount of data from your source table and to a destination table as quickly as possible. The amount of time to copy and the explosion of the log file are the two points of concern during this activity.
Let us look at a table with large amounts of data. We can use sp_spaceused on a table to find out the physical attributes. The following screen-cap tells us that the faded out table has close to 7 millions rows of data with the total storage size of 37 GB approximately.
It took more thank 10 hours to copy the data in the source table to a destination table in another database of the same SQL Server instance via the using standard Insert-Select Transact-SQL statements. The byproduct is this operation was large log file. The solution to deal with this issue would be the one where the copy action is not logged at all or is logged at the minimum. This would reduce the I/O and will speed up the copy process.
Bulk_Logged Recovery mode (plus “Select Into”) and BCP Utility are the two tools that can be used to achive faster copying times with no or minimal amount of transaction logging. Following is the usage of technique # 1 via using the SELECT Into clause in tandem with the Bulk_Logged recovery mode to copy the same table. The first step in this process is to programtically or manually set the database recovery mode to “Bulk-logged”
--Programatically setting the Recovery mode USE master; ALTER DATABASE DemoX SET RECOVERY BULK_LOGGED;
You can manually set the recovery mode by accessing the Database properties dialog box:
select * into DemoX.dbo.Employee from Demo.dbo.Employee USE master; ALTER DATABASE DemoX SET RECOVERY SIMPLE;
Copy Time: 2.75 hours. This is pretty decent considering that the traditional copying took more than 10 hours to do the same job. My next goal is to use the BCP utility to copy the same table and compare the timings. Here is the basic idea of using this utility
Step 1: Export data to a file using the BCP utility.
Step 2: Import data into the destination table using the BCP utility.
Will post my results soon.