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

  • 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

  • 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.

  • 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.

  • 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.

  • 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

  • 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

  • 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)

  • 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)

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

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