How to update SQL table from other table when they are on different servers

90 views Asked by At

I've already run the following command to include another server instance.

EXEC sp_addlinkedserver @server='Server'

Now I'm trying to synchronize these databases using this:

UPDATE
    [Server].[ServerDB].[dbo].[tableName] 
SET 
    [Server].[ServerDB].[dbo].[tableName].[columnName] = [LocalDB].[dbo].[tableName].[columnName]
FROM 
    [Server].[ServerDB].[dbo].[tableName], [LocalDB].[dbo].[tableName]
WHERE 
    [Server].[ServerDB].[dbo].[tableName].id = [LocalDB].[dbo].[tableName].id  

This gave me the following error:

The objects "LocalDB.dbo.tableName" and "Server.ServerDB.dbo.tableName" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

So after fiddling around with it I tried various renditions of this:

UPDATE
    [Server].[ServerDB].[dbo].[tableName] 
SET 
    [Server].[ServerDB].[dbo].[tableName].[columnName] = [LocalDB].[dbo].[tableName].[columnName]
FROM 
    [Server].[ServerDB].[dbo].[tableName] serverDB
INNER JOIN
     [LocalDB].[dbo].[tableName] localDB
ON 
     serverDB.id = localDB.id 

But they all give me some sort of rendition of the error:

The multi-part identifier "Server.ServerDB.dbo.tableName.allrows" could not be bound.

Can someone spot what I'm missing?

2

There are 2 answers

2
Donal On BEST ANSWER

You need to use this syntax when updating with a join:

UPDATE s
SET s.[columnName] = l.[columnName]
FROM 
    [Server].[ServerDB].[dbo].[tableName] s
INNER JOIN
     [LocalDB].[dbo].[tableName] l
ON 
     l.id = s.id 
1
Stuart Ainsworth On
UPDATE
    serverDB
SET 
    [columnName] = localdb.[columnName]
FROM 
    [Server].[ServerDB].[dbo].[tableName] serverDB
INNER JOIN
     [LocalDB].[dbo].[tableName] localDB
ON 
     serverDB.id = localDB.id