we run Microsoft SQL Server 2008 R2 on production server. We need create new tables and load a big amount of data on them (roughly 1 TB!)

I am wondering if it is possible to insert this dataset on a second test database server to avoid overloading the production server.

Once completed i would like to move the newly create tables in the production db in a "clever" way, avoiding to perform again the inserts.

What do you suggest? For instance, Is it possible to load the new dataset on separate filegroup and then move the files to the production db?


  • Are you using SQL Enterprise edition ? If yes, partitioning with filegroups will be the best option. – Kin Shah Feb 7 '14 at 16:30
  • Thanks for your answer. Could you please shortly describe the steps you think are necessary to complete the requested task? – user33968 Feb 7 '14 at 16:52
  • Is I/O the performance concern, or something else? What is the data source you'll be loading from? – Jon Seigel Feb 7 '14 at 17:39

You can accomplish this a couple of different ways, but probably the best way would be with a table partition. First you create your filegroups in the database and add at least one file to each. Make your existing table a partitioned table by following the wizard (there's more here than meets the eye. You will need to read up on table partitioning). Create a staging table that matches the structure of your existing table, but without any indexes or constraints or anything. Load your 1TB of data into the staging table. Once it is all loaded, then add your indexes and contraints and stuff. Then, you ALTER TABLE stagingtable SWITCH TO existingtable.

Since you didn't even know that table partitioning existed, you'll want to educate yourself on what a table partition is before you up, jump and implement in production: http://www.brentozar.com/sql/table-partitioning-resources/

Not only should you read up on best practices, but you will want to try it in your test environment first: http://msdn.microsoft.com/en-us/library/cc966380.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.