CODEWITHSUNDEEP
sqlsql-serversql-server-2008
thomas
thomas

Reputation: 61

Updating values in a column from another table on another server

Suppose I have table T with columns 'Name' and 'ID.' Table T exists on servers S1 and S2, and holds the same data in each (several thousands of rows). Now suppose the 'ID' column gets completely wiped out on S2's table T. What is the most efficient way to repopulate it using the version of T on server S1?

Upvotes: 0

Views: 58

Answers (2)

Timothy Walden
Timothy Walden

Reputation: 676

If tables are exactly the same I’d just delete all data from damaged table and then use SQL Server Export/Import wizard to populate this table using data on other server.

Another option is to use queries for this but you’ll first need to create a linked server. After that you can go with something like this.

--delete all data from damaged table
DELETE FROM S2.T
--copy all data from secondary server
INSERT INTO S2.T (ID, Name)
SELECT S1.T.ID, S1.T.Name
FROM S1.T

Note: this assumes your data is exactly the same on another server. If this is not the case don’t execute this because it could potentially cause damage.

Upvotes: 0

peterm
peterm

Reputation: 92785

Being connected to S2 and having a link to S1 established and assuming that you have a field or a group of fields that can help uniquely identify rows in S1.T and S2.T you can try to issue an UPDATE statement like

UPDATE t2
   SET t2.id = t1.id
  FROM T t2 JOIN S1.<dbname>.dbo.T t1
    ON t2.somefield = t1.somefield
-- AND t2.otherfield = t1.otherfield

Note: do SELECT first

Upvotes: 1

Related Questions