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 |
|
AsLorenz
Starting Member
2 Posts |
 Posted - 2010-04-01 : 09:48:22
|
| Hi all, I would like to know which is the best method (as performance) to import data from a remote SQL server to another SQL server.We have a list of tables to import, and in a part of that tables the entire table, in other cases we only select a part of the data in these. We declared the remote server as linked server and actually we're using the DTS to import the data.The DTS is working and is quick but very frequently the process is creating locks in tempdb.I want to abandon the DTS and find another solution also if probably has not the same performances.So, if not the DTS, what is the best method ?I think that OPENQUERY is a good solution but I want to know if there are better solutions.So here you can find the version of the source and destination SQL server.Source: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Destination:Microsoft SQL Server 2005 - 9.00.4035.00 (X64)Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) Thank in advance for your help.Lorenzo |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-01 : 11:58:56
|
| You can setup a linked server connection and use four part naming convention or OPENQUERY to import data sql server to your server. If you dont want to persist with a linked server connetion, you could use OPENROWSET for ad hoc access------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Data importation from remote SQL server, best perf