Tuesday, August 5, 2008

Copy data from local table to remote database table

I need to do this every now and then and as usual forget the syntax or what I did last time to do this. I need to copy some of the data in a table to an identically structured table on another (different) database running on another Server. I do this from SQL Server Management Studio (SSMS) 2005.

  1. Using Server Objects > Linked Servers I link the remote server to my local server. (You could also connect to remote server 1 and link to remote server 2 and do this between 2 remote servers.)
  2. If the target table has an Identity key I usually disable this by setting it to no and then after the copy I set it back to yes.
  3. The syntax to copy the data is:
    insert into [255.255.255.255].dbname.dbo.tableName
    select * from dbname.dbo.tableName where ColID=[some condition]

If the "local" DB is a remote server then precede that DB name with the IP address in square brackets as well.

Another way to do the data transfer is to specify the columns after the first tableName (in parenthesis) and instead of the * (without parenthesis) and omit the identity column and let SQL Server generate that value for you if you don't need to copy it. This is the syntax:

insert into [255.255.255.255].dbname.dbo.tableName (col1, col2, col3) select col1,col2,col3 from dbname.dbo.tableName where ColID=[some condition]

4 comments:

  1. Hmm. The picture looks more like Guy Ellis Beach.

    ReplyDelete
  2. But Where is the Login & Password for Remote Connection to Database.Did it works?
    Mehboob Yousafzai

    ReplyDelete
  3. but if remote server has password then how to put username and password in connection string. also how to retrive table valued function

    ReplyDelete
  4. please tell me the step by step procedure to transfer my database from localhost to remote server.

    ReplyDelete