I need to copy data from a DB/table on server1 to a table in a DB on server2

• Dave.Gardner

  Newbie

  Points: 4

  More actions

  March 24, 2009 at 11:21 am

  #136030

  I have SQL Server 2000 on server1 and SQL Server 2005 on server2. I need to copy data from table1 on server1 (SS2000) to a table on server2 (SS2005). To do this I first need to query table2 on server2 to get the criteria to be used for filtering the data to copy from table1 in database1 on server1. Something like this:

  DECLARE @LastCopy as smalldatetime

  SELECT @LastCopy = MAX(EntryDate) FROM Server2.DB2.Table2 WHERE Criteria2_1 LIKE 's0%'

  INSERT INTO Server2.DB2.Table2

  VALUES

  (

  SELECT Fields... FROM Server1.DB1.Table1 WHERE EntryDate > @LastCopy AND Criteria1_1 LIKE 's0%' AND Criteria1_2 LIKE '0601%'

  )

  What's the easy way to accomplish this?

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 24, 2009 at 11:35 am

  #965453

  Hi

  Did you have a look for linked servers? This should be your solution. Here a little sample for the usage:

  DECLARE @LinkName SYSNAME SET @LinkName = 'LinkName'

  DECLARE @SrvName SYSNAME SET @SrvName = 'Your Sql2k'

  DECLARE @LocalLogin SYSNAME SET @LocalLogin = NULL

  DECLARE @RmtLogin SYSNAME SET @RmtLogin = 'remote login name'

  DECLARE @RmtPwd SYSNAME SET @RmtPwd = 'remote password'

  IF NOT EXISTS (SELECT * FROM Master..Sysservers WHERE IsRemote = 1 AND SrvName = @LinkName)

  BEGIN

  EXECUTE sp_addlinkedserver @server = @LinkName,

  @srvproduct = '',

  @provider = 'SQLOLEDB',

  @datasrc = @SrvName

  EXECUTE sp_addlinkedsrvlogin @rmtsrvname = @LinkName,

  @useself = 'false',

  @locallogin = @LocalLogin,

  @rmtuser = @RmtLogin,

  @rmtpassword = @RmtPwd

  END

  GO

  SELECT TOP(1) * FROM LinkName.master.sys.objects

  Greets

  Flo