copying Data from Big Data Table

Question

I have a table with around 400 GB data I want to copy some data to some other temporary database to make some more space. Can you please give me a efficient way to do that?

My DBMS is SQL server 2014 standard edition.

Answer 1

You want to use a minimally logged inserts. If your temp/scratch database is on the same instance you can simply do.

SELECT * INTO DATABASE.SCHEMA.TargetTable FROM SourceTable;

The above statement create the table in the target database and bulk insert the data.

If you want to copy the data to a different instance you are best off using SSIS.

Answer 2

Is the source table partitioned? If so, partition switching is a great way to move large blocks of rows from a "hot" table to an "archive" one.

At that size I would expect the DB to have many files. You could put old data in one file, make it read only and perform file backups of the hot data rather than full backups of the whole DB. Restore gets more complicated, however, and you would want to practice regularly to ensure you can get it right when needed.

BCP is a handy utility for copying chunks of data. Though much less capable than SSIS it requires less setup.

And investigate the various compression options available. We've found that queries can be faster due to the reduced IO. YMMV, of course.