Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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'.")

like image 455
James123 Avatar asked Jun 20 '11 15:06

James123


2 Answers

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) 
like image 172
a1ex07 Avatar answered Oct 12 '22 23:10

a1ex07


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> 
like image 37
Sha Avatar answered Oct 12 '22 21:10

Sha