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

Question

I am trying to update database fields from one SQL Server table to another.

Our production SQL Server is [spdbprod.test.com\spprod], our QA server is [spdbQA.test.com\spQA].

I need to update table in production from QA table. I using this SQL statement but, it is giving an error.

UPDATE 
    [spdbprod.test.com\spprod].[aspnetdb].[dbo].[Communities_Groups] as t1
SET 
    t1.Show = (Select t2.show from [spdbQA.test.com\spQA].[aspnetdb].[dbo].
[Communities_Groups] as t2 where t1.GroupID = t2.GroupdID)

What I am missing here? Error: UPDATE. ("Incorrect syntax near the keyword 'as'.")

If you post something like this and tease us with *it gives an error*, it would be best practice to **tell us** what the exact error is! That would be a tremendous help to see what the probelm might be.... — marc_s, Jun 20 '11 at 16:16
@marc_s: I think one obvious error is table alias in `UPDATE`. ("Incorrect syntax near the keyword 'as'.") — a1ex07, Jun 20 '11 at 16:29
@a1ex07: that's a guess - a good one (upvoted you for that good guess), but still a guess.... and it's about general principle - don't just say "there is an error" - if you say that - TELL US what that error is! — marc_s, Jun 20 '11 at 16:30

score 32 · Accepted Answer · answered Jun 20 '11 at 15:50

32

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)

answered Jun 20 '11 at 15:50

a1ex07

36,826
12
90
103

1

The form : "UPDATE t SET field=val FROM table1 t" also works. – Moe Sisko Jun 21 '11 at 05:26
1

Apparently, the `[dbo]` portion is more important than I would have thought. Nevertheless, you just saved my butt! Greatly appreciated. – Drew Chapin Dec 20 '13 at 14:31

Sha · Answer 2 · 2023-08-06T14:12:32.953

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>

For those wanting to copy all data (INSERT) from a table from one server to another, it can be done with a command like this:

SELECT * INTO TargetTable 
FROM 
[SERVER-NAME or IP, PORT].[SourceDatabase].[dbo].[SourceTable]

This will create a new table called TargetTable using data from the SourceTable at the SourceDatabase at SERVER-NAME or IP, PORT.

score 2 · Answer 3 · edited Apr 08 '22 at 18:45

2

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

edited Apr 08 '22 at 18:45

TylerH

20,799
66
75
101

answered Jun 20 '11 at 15:53

Harv

523
3
8

Humayoun_Kabir · Answer 4 · 2020-09-10T11:19:02.870

You need to add a Linked Server under Server Objects
You can name that Linked Server either with "CHOOSEN-NAME" or [ipaddress , port number]

how to add a linked server using tsql , please check this link : how to find linked server

for an example purpose suppose i have named the linked server "DESTINATION_SERVER" , database name is "DESTINATION_DB" and table name is "DESTINATION_TBL". then from your source server your query could be like this below:

UPDATE t1  
SET t1.updatecolumn = t2.updatecolumn
FROM [DESTINATION_SERVER].[DESTINATION_DB].[dbo].[DESTINATION_TBL] t1
INNER JOIN [SOURCE-SERVER].[SOURCE_DB].[dbo].
[SOURCE_TBL] t2 ON (t1.matcingcolumn = t2.matchingcolumn)

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

4 Answers4

Linked