Update database table from one SQL Server database table to another?

I believe you have to have a database link (linked servers) for this to work.

I do not have access to two SQL servers here at work so I cannot test it, but I sure that you need the link.

Do you have a linked server setup?

Here is a URL that may help http://msdn.microsoft.com/en-us/library/ms188279.aspx

You are using table alias in a wrong way. You cannot do UPDATE table1 t SET field1=val, you have to write UPDATE table1 SET field=val (Or UPDATE table1 SET field=val FROM table1 t). So change your query to

UPDATE [spdbprod.test.com\spprod].[aspnetdb].[dbo].[Communities_Groups]   
SET Show = t2.show
FROM [spdbprod.test.com\spprod].[aspnetdb].[dbo].[Communities_Groups] t1
INNER JOIN [spdbQA.test.com\spQA].[aspnetdb].[dbo].
[Communities_Groups] t2 ON (t1.GroupID = t2.GroupID)

I know this has been answered already but this worked for me.

• Add a Linked Server under Server Objects | Linked Servers: Microsoft documentation.

• Name the Linked Server [SERVER-NAME or <some ipaddress>, <some-port>]

  e.g. [10.0.0.200,2345] - I am using port 2345 but the standard MS SQL port is 1433.

Example:

• We have a [Customers] table
• We want to update [CustomerAddress]-field for CustomerId = 123
• We want to use backup data from a server called [backupServer]
• The [backupServer] is the machine where we execute the SQL

This is the SQL-code:

UPDATE production
SET
    CustomerAddress = backupServer.CustomerAddress
FROM 
    [10.0.0.200,2345].[ProductionDatabase].[dbo].[Customers] production
INNER JOIN 
    [BackupDatabase].[dbo].[Customers] backupServer
        ON 
            production.CustomerId = backupServer.CustomerId
WHERE 
    backupServer.CustomerId = 123

Generalized format:

UPDATE production
SET
    columnName = backupServer.columnName
FROM 
    [SERVER-NAME or IP,PORT].[ProductionDatabase].[dbo].[tableName] production
INNER JOIN 
    [BackupDatabase].[dbo].[tableName] backupServer
        ON 
            production.SomeId = backupServer.SomeId
WHERE 
    backupServer.SomeId = <id>