Copying Data From One Table Another Table

[Creeder]

Hi all,

How would i copy data from table A to another table B(with some new columns) where both table is located at different servers?

Eg. Server A
Table A : Columns: Cust_id,name,lastname

Server B
Table B: Columns: Cust_id,name,lastname,birthday

Any help is greatly appreciated.

 

[StevenK]

I'm relatively new to SQL server databases but will be tackling a similar situation in the near future. The way I intend to tackle it is as follows. If you're wanting to copy data from a remote server to a local server you should first link the remote server using the 'sp_addlinkedserver' command such that the local server recognises the linked remote server. Following this you can run a query something like 'INSERT INTO [local-server-name].[database1].dbo.[Table1] ([Field1], [Field2], [Field3]) SELECT [Field4], [Field5], [Field6] FROM [remote-database-name].[database2].dbo.[Table2]'
This may not be the correct syntax as its off the top of my head at the time.
Please bear in mind that this theory is untested and is open to comments from more experienced SQL server database users ... it'll help me.
Thanks
Steve.

 

[foxdev]

StevenK's approach is the first one I thought of as well. You can also use DTS, but for my money do what you can in "plain" T-SQL when possible. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org

 

[Creeder]

Thanks guy it's working now.