当其他表在不同的服务器上时，如何从其他表更新SQL表

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.

LocalDB.dbo对象”。表”和“Server.ServerDB.dbo。FROM子句中的tableName"具有相同的公开名称。使用相关名称来区分它们。

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.

Server.ServerDB.dbo.tableName多部分标识符”。allrows"不能被约束。

Can someone spot what I'm missing?

有人能找出我所缺少的东西吗?

2 个解决方案

#1

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

在使用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 

#2

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