Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2012-04-13 : 13:42:12
|
| I have SSMS installed on a local PC. I connect to servers PROD and TEST. I want to transfer all the data from a large table on PROD to TEST. I have linked servers set up which use my SQL Account. The query is a simple INSERT statement but it takes hours. I think OPENQUERY may be better, though than the linked servers. I actually want the data to go directly from PROD to DEV and not come through my PC, but I am not sure how to tell what is actually happening. I do have stored procedures to do this, but they take long as well.Duane |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-13 : 13:47:48
|
| SSIS may be faster. If it is a simple copy, you can use the Import/Export Wizard to do this (and to create a package if you need to use it repeatedly). |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-13 : 14:58:25
|
| OPENQUERY will not be better than SSIS or bcp. bcp requires using a file as intermediate data storage (export to file->import file to database), whereas SSIS can use a bulk copy task as a data source to feed directly to your destination table. You'll want to investigate using Bulk Copy as much as possible, this generally gives the fastest performance. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-04-13 : 15:01:58
|
| there is also backup and restore?<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2012-04-13 : 15:28:57
|
| Thank you for the input. I will probably end up using SSIS, but I was hoping to just keep things simple with the stored procedures and the job used to run them. Server A is SQL 2000 and I don't have access to either machine physically or remotely except through the SQL queries, so I am not sure how that will all work out when my PC is out of the equation as these may have to be repeatable occasionally.Duane |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2012-04-14 : 02:21:14
|
you could use bcp from a stored proc if xp_cmdshell is enabled.
elsasoft.org |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-17 : 03:10:16
|
| If its a simple import/export without any modifications to data then I would use BCP. BCP should perform better, so, for simple import/export I would recommend using bcp.N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
|
|
|
Fastest way to transfer data from large table