Is is possible to use DTS to Update a table in database on a different server?
-
Example:
Server 1...Table1
Server 2...Table2
I would like to update server...table2 with records from server1..table1 where server1...table1.ID = server2...table2.ID.
How can i do this using DTS?
-
I do not think you can. You would have to use a linked server and write a script to compare the two tables
-
Another work around would be..u just move the Server1 table to server2...update the server2 table and then delete the copied table on server2
-
You should be able to add a linked server and then use that servername in your query: [LINKED SERVER NAME].[DATABASE NAME].[owner].
. Here is a link to MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp
-
Another way you could create 2 connection one to server1 and other 1 to server 2 and update what ever you want..
-
No matter how you choose to do it, you'll have to create a linked server on the server where you are running the DTS package. The simplest way in DTS to keep table2 in sync with table1 is to delete the records in table2 and then insert the records from table1. Use an execute SQL task for the first step and then an import data task or BCP to perform the second task.
Perhaps setting up replication between the servers and tables would be a better route or a best practice to implement.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply