Post reply

Firehose Anyone? What's the most efficient way to insert 11 million records from one table to another?

  • November 12, 2009 at 9:24 pm

    #142991

    I have an INSERT statement that is required to firehose 11 million records into a table.

    It is taxing the server overall, but especially the log file. Logging is not required in this case, and is needless overhead.

    Can I use a hybrid of BULK INSERT and/or OPENROWSET to perform a table to table insert without Logging? I could export the 11 million records to a flat file, and bulk insert them back, but I am loath to implement such a clumsy solution.

    Is there another efficient solution for my problem?

    Thanks

  • November 12, 2009 at 9:35 pm

    #1078697

    Any way you do it it has to be logged, there is no way to turn it off. The best thing you can do is to perform the insert in batches as well as run periodic t-log backups to manage t-log growth.

  • November 13, 2009 at 2:22 am

    #1078753

    Bulk insert, bcp and SELECT INTO are all minimally logged if the database's recovery model is something other than FULL. There are other limitations, check Books Online for the details of minimally logged operations.

    There's no way to do an unlogged data modification in SQL, for good reason.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 13, 2009 at 6:42 am

    #1078882

    GilaMonster (11/13/2009)

    Bulk insert, bcp and SELECT INTO are all minimally logged if the database's recovery model is something other than FULL. There are other limitations, check Books Online for the details of minimally logged operations.

    There's no way to do an unlogged data modification in SQL, for good reason.

    I keep forgetting that, sorry <leaves sheepishly>.

Viewing 4 posts - 1 through 3 (of 3 total)

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