Updating Tables on a Remote Server

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

SQL Server Development (2000)

Updating Tables on a Remote Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-22 : 09:49:48

Dan writes "I am trying to Update tables on a Remote Server. Here is an example of what I am trying to do:

UPDATE [ServerName].DatabaseName.Owner.Table1
SET Field1 = T2.Field2
FROM Table2 T2
WHERE [ServerName].DatabaseName.Owner.Table1.Field3 = T2.Field4

However '[ServerName].DatabaseName.Owner.Table1.Field3' is invalid syntax, so I need another solution. I have come up with 2 different ones. The first is:

UPDATE [ServerName].DatabaseName.Owner.Table1
SET Field1 = T2.Field2
FROM Table2 T2
JOIN [ServerName].DatabaseName.Owner.Table1 T1
ON T1.Field3 = T2.Field4

However, joining back to the table on the remote server is a very inefficient way of doing it. My other solution is to create a view of Table1 and then do this.

UPDATE Table1_View
SET Field1 = T2.Field2
FROM Table2 T2
WHERE Table1_View.Field3 = T2.Field4

However, I really dont know if that is any more efficient than the Join solution. If anyone knows whether the view solution is a good way of doing it or knows of a better way to Update tables on a remote server, please let me know. Thanks

Dan"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-22 : 11:32:08

Using the view and the fully qualified table name should produce the same query plan.

The problem with remote updates is that the data has to be passed to one server to do the join.
Make sure the servers are set as collation compatible so that it can choose the best server.
You may find it faster to populate a table on the remote server and then call a stored procedure to do the update localy.

Dealing with this sort of thing I always try to trickle feed the data across so that when the update takes place everything is already there and you get notification during the day that there is a problem with the remote link. Don't know if that is possible.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources