Perform a data update from one sql server to another sql svr

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Perform a data update from one sql server to another sql svr

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-14 : 09:24:49

Joseph writes "We have a web based program that feeds data into our local sql server database. Because of a recent partnership between our company and another company we are trying to share data from our sql server to their sql server seemlessly and automatically. We have already succesfully created the simulation code for the automated process, but the simulation merely updates data from one database to another database within our server. Now we want the process to move data from our server to their server using the sql code as per the simulated code we currently have running. The question is what kind of connection code must we program to connect from our server to their server remotely to complete the sql updates. We have been supplied server ip addresses, port numbers, username, passwords, domains, server names, and instance names. We thought this was going to be straightforward but have since discovered that there does not appear to be any example code to accomplish this data update process. If you have any suggestions, it would be greatly appreciated.

Joe"

Kristen
Test

22859 Posts

Posted - 2006-02-14 : 11:53:48

If you are doing


UPDATE U
SET ...
FROM MyLocalTable AS L
     JOIN RemoteServer.RemoteDatabase.dbo.MyRemoteTable AS U
         ON U.MyPK = L.MyPK
WHERE ... rows are different or some other criteria ...

or an equivalent

INSERT INTO RemoteServer.RemoteDatabase.dbo.MyRemoteTable
SELECT *
FROM MyLocalTable
WHERE NOT EXISTS ( ... )

then the process is likely to be pretty slow, particularly if lots of rows are involved.

We use an UPDATE DATE column on the tables that we synchronise in this way and then INSERT all changed rows into a temporary table - no JOINs etc. involved. Then we run a process at the remote end to manipulate the data into the relevant table(s) at the remote end.

Generally we do this as a "Pull" running at the Remote end - i.e. the Remote computer is in charge of the process and pulls the data and then manipulates it.

Pulling the data is done using

INSERT INTO #TempTable
SELECT *
FROM OPENQUERY(MyOtherServer, 'SELECT * FROM MyTable WHERE MyUpdateDate > ''some date & time''')

This avoids distributed transactions, and has proven to be fast enough (although DTS would be another fast method, and using BCP to exporting the data to files and then to import it at the far end may well be faster still, but adds some complexity in maintenance & support)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources