Copy data between DBs on separate servers

• sql60190

  SSC Veteran

  Points: 296

  More actions

  March 12, 2009 at 1:21 pm

  #135497

  Is it possible to do something like:

  [Code]

  BEGIN TRANSACTION

  INSERT into stagingDatabase.DestinationTable

  SELECT *

  FROM productionDatabase.SourceTable

  COMMIT TRANSACTION

  [/Code]

  when stagingDatabase is on server A and productionDatabase is on server B? (These servers are not linked, nor will they ever be.)

  Thanks!

• Maxim Picard

  SSCrazy

  Points: 2052

  More actions

  March 12, 2009 at 1:35 pm

  #958678

  You can't query a remote server without specifying a Linked Server, you will get an error that server is not in sysservers.

  Check for sp_addlinkedserver in BOL.

  After that you should not have any problem running a query similar to what you have posted.

  Make sure to use fully qualified name:

  [LinkedServer].[Database].[owner].[Table]

  hth!

• Mohit K. Gupta

  SSChampion

  Points: 12130

  More actions

  March 12, 2009 at 2:25 pm

  #958730

  You can look at using OPENROWSET function, look at link.

  Thanks.

  [font="Arial"]---

  Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
  Microsoft FTE - SQL Server PFE

  * Some time its the search that counts, not the finding...
  * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

  How to ask for help .. Read Best Practices here[/url].