4

I have a table in a local MS SQL Server database that has 72 columns and over 8 million records. Most of the columns are floats except for the primary key which is an INT Identity column. The table also has 3 indexes.

These records are the saved output from a utility I wrote that processed around 100 Billion calculations on different target audiences. I have saved the best and worst results for each target audience for future lookup. I would now like to move all these records into my staging server's database.

What is the best way to copy transfer these records?

I tried Red Gate, and it just dies and says out of disk space. My disk has 22 GB free. Thanks.

Nick Chammas
  • 14,810
  • 17
  • 76
  • 124
Bobby Ortiz
  • 141
  • 3

3 Answers3

3

Your operation will most likely be a logged operation as you're performing an insert.

Monitor the log file growth and you should see that's what is utilizing your remaining disk space.

Consider inserting using a minimally logged approach, these include:

  • BULK INSERT
  • INSERT...SELECT
  • MERGE with Trace Flag 610

I have also found an insert to a column with an identity specification will also cause logging to occur.

Links: http://blogs.msdn.com//b/sqlserverstorageengine/archive/2008/02/05/bulk-logging-optimizations-minimal-logging.aspx http://www.sqlmag.com/article/tsql3/minimally-logged-inserts

Also: I would recommend inserting into a heap and then apply your indexes unless your data has been pre-sorted in the source database.

Consider defining the identity value in your source database, select all the data into the destination and then apply the identity specification and set the seed.

MEL
  • 81
  • 1
  • 5
2

The BCP utility in SQL would be a good candidate for this task. Check out @Mark Storey-Smith answer to a similar issue.

Also make sure you have adequate space for your extracts. You can combine the export and import into a batch file to create a single batch program.

Community
  • 1
StanleyJohns
  • 5,982
  • 2
  • 25
  • 44
1

You may use smth like this:

DECLARE @min int, @max int, @step int = 10000 --or any other batch size

SELECT @min = MIN(Id), @Max = max(id)
FROM YourTable

WHILE @min <= @Max
BEGIN
  INSERT YourStagingDB..StagingTable(columns)
  SELECT columnlist
  FROM YourTable
  WHERE Id >= @min AND Id < @min + @step

  SET @min = @min + @step 
END

this will prevent your log-file overgrowth

Oleg Dok
  • 3,407
  • 26
  • 29