Fastest Method to copy data Table to Table on the same Server

Question

Fastest Method to copy data Table to Table on the same Server

Christopher West

Ten Centuries

Points: 1209
More actions
October 7, 2009 at 7:49 pm

#385530

I want to copy tables from one DB to another on the same server. Source Tables are non-unicode, target tables are unicode. I created the target tables with just the clustered index defined. I then ran the following SQL script
Insert into TargetTable
select * from SourceTable
The script ran in 2 hours, created a 35 GB table and the DB log was 38 GB. I then was reading about the bulk-logged recovery model and how it would allow minimal logging in certain situations (empty target table and tablock optimizer hint set). I then created a DB with the bulk-logged recovery model and tried the following script:
Insert into TargetTable WITH (TABLOCK)
select * from SourceTable (nolock) --figured nolock might speed things up a bit--
This actually ran in 2 1/2 hours and created a 35 GB table with a 39 GB log. Does anyone know the proper way to get minimal logging using the bluk-logged recovery type? ... or a better method of transferring the data from table to table? Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhaskar Basak SSC Eights! Points: 967 More actions · Answer 1

If you are using SQL Server 2005 then you can try SSIS with Data Flow Task. Define Set Rows Per Batch & Maximum Insert Commit Size under OLE DB Destination to some finitie value e.g. 10000,100000.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Well, you are doing the entire copy in a single transaction which is why the log file is growing so much. If you batch it with TX Log backups between batches or you go with SIMPLE recovery and CheckPoints your log shouldn't grow so much.

Do you have indexes on the table already? If so you may want to drop the indexes and then re-create

them AFTER the load.

How big is the source table? Remember going from non-unicode to unicode will double the storage required for string data.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Christopher West Ten Centuries Points: 1209 More actions · Answer 3

Thanks Jack,

I understand that I can break the inserts into batches and then I could create smaller TXN logs. But, it will still mean ~40 GB of data will have been written to the TXN logs (let's say 8 X 5 GB TXN logs). My hope is to reduce the amount of logging that occurs. My question was regarding the bulk-logged recovery model and reading something that stated that one could reduce logging on inserts to an empty table by using the bluk-logged recovery model. When I turned on the bulk-logged recovery model and reran my insert (see above) I still ended up with the same size TXN log, so either I did something wrong in the insert statement or the bulk-logged recovery model doesn't reduce the amount of logging that occurs for my particular insert.

I am inserting to an empty table that only has a Clustered Index defined. The table is ~35 GB, log ended up being ~39 GB.

I'm starting to think that the perhaps the bulk-logged recovery model option fully logs data pages and minimally logs index pages. If that is the case then the bulk-logged recovery model doesn't help me in this situation because I only have data pages. I thought SQL Server might skip logging altogether and just truncate the table if there was a failure. In other words, if SQL Server knows it started with nothing in the table and the insert fails it can just restore the table to empty rather than rolling back individual inserts in the TXN log.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Logging is the way that SQL Server guarantees consistency. By reducing batch size and backing up the transaction log you will not/should not experience log growth which can be an expensive operation.

The bulk logged recovery model has no effect on an insert into statement. See http://msdn.microsoft.com/en-us/library/aa173529(SQL.80).aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Can I ask a stupid question...

What is the problem you are trying to solve with the copy of data. It seems like a lot of effort to take this copy of data..

CEWII

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

USE SourceDB

SELECT CAST(columnname1 as NVARCHAR(whatever)) AS columnname1,

CAST(columnname2 as NVARCHAR(whatever)) AS columnname2,

etc, etc...

INTO dbo.DestinationTable

FROM SourceDB.dbo.SourceTable

... then create the index...

Of course, the code above is for when the table doesn't already exist.

If that doesn't work for performance (and it may not), then try BCP OUT to a file and BCP it back into the new table. Behind the scenes, that's what replication does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

PS... don't forget to set the recovery mode to BULK LOGGED.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)