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

Question

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?

I run other commands interacting with the `[Server].[ServerDB].[dbo].[tableName]`, so I'm pretty confident it's attached. It's just this update that gives me problems. — Charles Clayton, Jun 17 '15 at 16:46
It looks like it's trying to interpret `[allrows]` as a column name. — APH, Jun 17 '15 at 16:47
Sorry, that was a bad example, `[allrows]` is a column name. I meant to say `[allColumns]`. — Charles Clayton, Jun 17 '15 at 16:51
Can you test the linked server?: `SELECT * FROM [Server].ServerDB.dbo.tableName`. Does that return rows? — Trinculo, Jun 17 '15 at 16:51
What if you change the `FROM` to the local DB and `INNER JOIN` on the server? — Trinculo, Jun 17 '15 at 16:56
One more suggestion: Try removing the aliases all together and typing the full name. (remove serverDB and localDB) and then on the `INNER JOIN` use the full `[Server].ServerDB.dbo.tableName.id = [LocalDB].dbo.tableName.id` — Trinculo, Jun 17 '15 at 17:02

score 3 · Accepted Answer · answered Jun 17 '15 at 17:03

3

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

answered Jun 17 '15 at 17:03

Donal

31,121
10
63
72

1

Okay, thanks! I'm new to SQL and that looks odd to me because it seems like you're using a variable name `s` and `l` in the `SET` before defining them in the `FROM` and `INNER JOIN` – Charles Clayton Jun 17 '15 at 17:08
Yes, the syntax for update with join was the issue. It is a bit wierd in SQL Server. s and l are called aliases, you have used serverDB and localDB, I used s and l - but you can use whatever alias you want. – Donal Jun 17 '15 at 17:12

score 2 · Answer 2 · answered Jun 17 '15 at 17:03

2

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

answered Jun 17 '15 at 17:03

Stuart Ainsworth

12,792
41
46

Yep, that looks like it did it! – Charles Clayton Jun 17 '15 at 17:07

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

2 Answers2