Efficiently moving large sets of data between SQL Server tables?

Question

I have a rather large (many gigabytes) table of data in SQL Server that I wish to move to a table in another database on the same server.

The tables are the same layout.

What would be the most effecient way of going about doing this?

This is a one off operation so no automation is required.

Many thanks.

Answer 1

If it is a one-off operation, why care about top efficiency so much?

SELECT * INTO OtherDatabase..NewTable FROM ThisDatabase..OldTable

or

INSERT OtherDatabase..NewTable
SELECT * FROM ThisDatabase..OldTable

...and let it run over night. I would dare to say that using SELECT/INSERT INTO on the same server is not far from the best efficiency you can get anyway.

Answer 2

Or you could use the "SQL Import and Export Wizard" found under "Management" in Microsoft SQL Server Management Studio.

Answer 3

I'd go with Tomalak's answer.

You might want to temporarily put your target database into bulk-logged recovery mode before executing a 'select into' to stop the log file exploding...

Answer 4

If it's SQL Server 7 or 2000 look at Data Transformation Services (DTS). For SQL 2005 and 2008 look at SQL Server Integration Services (SSIS)

Answer 5

Definitely put the target DB into bulk-logged mode. This will minimally log the operation and speed it up.

Answer 6

You might want to take a look at bcp (bulk copy). http://blogs.techrepublic.com.com/datacenter/?p=319

We have moved tables in excess of 25GB very fast and with relatively little problems. The only limitation seems to be the amount of space on your hard drive.