Refresh a 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 SQL Server [spdbprod.test.com\spprod] , our QA server [spdbQA.test.com\spQA] .

I need to update a table during production from a QA table. I use this SQL statement, but it gives 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 am I missing here? Error: UPDATE. ("Invalid syntax next to the how keyword.)

Answer 1

You are using the table alias incorrectly. You cannot do UPDATE table1 t SET field1=val , you need to write UPDATE table1 SET field=val (Or UPDATE table1 SET field=val FROM table1 t ). So change your request 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) 

Answer 2

I believe that for this you need to have a link to the database (linked servers).

I do not have access to the two SQL servers here, so I can not test it, but I'm sure you need a link. Do you have a linked server installation installed?

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

Harvey Sater