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.
- 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.)
- 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.
- 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]