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?
You need to use this syntax when updating with a join: