Geek Answers Handbook

Effectively moving large datasets between SQL Server tables?

I have a rather large (many gigabytes) data table in SQL Server, which I want to move to a table in another database on the same server.

Tables have the same layout.

What would be the most effective way to do this?

This is a one-time operation, so automation is not required.

Many thanks.

+6
sql-server-2005
source share
5 answers

If this is a one-time operation, why care so much about maximum efficiency?

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

or

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

... and let him run overnight. I would dare to say that using SELECT / INSERT INTO on the same server is not much better than you can get anyway.

+6
source share

Or you can use the "Import and Export SQL Wizard", which is located in the "Management" section in Microsoft SQL Server Management Studio.

+2
source share

I would go with Tomalak.

You might want to temporarily put your target database in recovery mode with a large write volume before you make a "selection" to stop hacking the log file ...

+2
source share

If SQL Server 7 or 2000 is accessing Data Transformation Services (DTS). For SQL 2005 and 2008, see SQL Server Integration Services (SSIS)

+1
source share

Definitely put the target database in bulk log mode. This will minimize the registration of the operation and speed it up.

+1
source share

More articles:


All Articles