Fastest way to copy a single large (30,000 rows) table from one database to another?

Question

I've got a large table in a SQL Server 2005 database and I'd like to copy it over to another database.

What's the fastest way to do this? Is there a shortcut to linking servers and doing inserts?

Answer 1

I don't want to hit anyone here. But so far, I didn't find a fast way to copy table. I am working on a big project and I usually handle table with 3-7 millions rows. When I want to back the table, my headache come.

Up to now: 1. BCP is definitively the fastest tool. But it can only handle simple datatype correctly. If you have nvarchar and has some strange chars '"&#2. It ususally doesn't work. Due to the high risk work with bcp. I recommend you forget it but only use it for very simple table. It is really SUPER fast.

2. Never mention export/import wizard or command. It is one of the lowest tool I has used. And it may broke during transport.

3. So far, is fastest way is to detach you database, copy it by harddisk(usually 20M/s). Even you data occupy 6G disk. it only take 300s ~ 5 minutes. And attach it on another machine. Remove tables you don't want. If you use any method suggest by others, I promise you won't get the transmission in an hour.

Answer 2

I think best option is to use export and import .

Answer 3

The EXPORT function of Sql Server Management Studio Express.

http://www.microsoft.com/downloadS/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

You need specify the destination server and tables, and the tool do all you need.

Answer 4

to improve the speed, disable all forms of logging and drop any constraints or referential integrity.

Answer 5

Try a command line utility called bcp for a bulk export, and Bulk Insert for the import.

See About Bulk Import and Bulk Export operations.