How to use tables from 2 different servers.

Question

I need to get to tables on 2 different servers and use them in a query. I thought if I did a bulk copy and put them on the main server, by truncating a new table (s) on the new server and then do the bulk copy. My boss said that will not work because the tables are too large. How can I do this.

2 tables are on server 1 and need to use them on server 2.

arep

chellappan pandiarajan · Answer

Hi If you use SQL SERVER then follow the below steps. By using the query EXEC sp_linkedservers check is there linked server between SERVER1 and SEVER2 If there is no linked server between the two then follow the below steps First you create a linked server between Server 1 and Server 2 by using sp_addlinkedserver or follow the steps that given in the link http://msdn.microsoft.com/en-us/library/aa560998%28v=bts.10%29.aspx after that it was easy to query you can query the table like below SELECT * FROM [MY_LINKED_SERVER_Name].[database_name].[dbo].[table_name] Or for a join use linked table with an alias (example): SELECT * FROM Table INNER JOIN [MY_LINKED_SERVER_Name].[database_name].[dbo].[table_name] LinkedTable ON Table.column = LinkedTable.column I think it will solve your problem. - Pandian - Mark us answer if it solves ur problem

A Repasky · Answer

Thanks that will probably work but I had an easier solution for me. I could get all the original columns from 3 tables on server 1 and then use bulk copy to put then in the table on server 2. The problem was that this was step 2 and in the first step it got a lot of columns and put them in a table already and I was trying to use that table with getting more infor from server 1. This was caused by too many table with too many columns in step 1. So step 2 had to pick up a few columns from a couple of tables on server 1. This is probably confusing but I get the info all together now. arep