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?
views:
778answers:
5
+2
A:
The EXPORT function of Sql Server Management Studio Express.
You need specify the destination server and tables, and the tool do all you need.
Zanoni
2009-07-02 20:26:47
+3
A:
Try a command line utility called bcp for a bulk export, and Bulk Insert for the import.
Brian
2009-07-02 20:27:33
Is this the fastest and reliable way?
Zanoni
2009-07-02 20:34:40
Honestly with software, there are probably at least a half dozen 'fast' ways to do something. It depends on your strengths. If all else fails, you could just export the table into a CSV and script the inserts.
Brian
2009-07-02 22:28:45
+1
A:
to improve the speed, disable all forms of logging and drop any constraints or referential integrity.
SQL Baba
2009-07-02 20:31:57
the easiest and most user friendly (in my view) in case of multi-server setup is to use SSIS on target server. Build SSIS package with connection to source and with no data transformations (obvious) into the db on your destination server
SQL Baba
2009-07-02 20:34:53
There is no way to disable logging in SQL Server. SIMPLE recovery simply tells the SQL Server that the log entries can be overwritten after they are checkpointed.
mrdenny
2009-07-03 04:15:46
i never knew we cannot disable logging completely in sql server. thanks for sharing info.
SQL Baba
2009-07-03 07:11:02
A:
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 '". 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.
Never mention export/import wizard or command. It is one of the lowest tool I has used. And it may broke during transport.
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.