Post reply

VLDB bcp issues

  • April 22, 2010 at 8:35 am

    #140490

    Hi all

    I have been asked to help on an issue within our company that I need a bit of advice on.

    Basically the company receives a large database with 300+ tables holding several billion rows of data combined. They have a requirement to move these tables into a single table in a separate database. The process they were using to move the data was failing on a regular basis so I changed it to use bcp to create a .dat file for each table and then bcp the data back into the new table. This runs fine and takes just short of 8 hours to do all of the tables. But they also need a primary key created on this new table which is basically an Identity column, but this process runs for at least a day (still running now) and there is limited disk space for the log to grow.

    Is there a more efficent way than simply altering the table to create the new column with an identity? Is there a way in bcp to create the identity as I add each table into the single table?

    Any help appreciated.

    Thanks.

  • April 22, 2010 at 12:39 pm

    #1156365

    hmm- my thought is that work you are trying to accomplish is going to take a long time regardless of what you do, but you mention log file size issues. Is this a production system? If not maybe go to simple mode for this?

    If that's not an option, what about this: create a table with the right columns, including your Pk. Do a bulk insert in batches- something like the old "while rowcount" loop- and backup the transaction log at the end of each loop.

  • April 22, 2010 at 12:54 pm

    #1156374

    Thanks for the reply!

    I have switched to Simple as it is a staging environment which saved me a bunch of time.

    I have actually solved the problem by adding an int col to the end of the bcp query in, and then adding the incrementing primary key to the table which is then incremented correctly as I run through each batch. Like you say, it is going to take a while but as it is in batches I can control what is getting completed rather than waiting for one big statement to fail and roll everything back.

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

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