0

I am trying to access tables from two different linked servers. Basically from one server I get the list of id's and from the other linked server, I need to get two other columns whose id's match.

I tried to get the id's first from one server and populated it in an excel. After this, I took all the data from other server and wrote a vb macro to pick the matching rows. This actually takes lot of time, and at mos times, excel hangs as there are lot of content to compare actually. Is there a really simple solution for this?

I am trying for something like the below one, but facing issues to get the results.

   select * from openquery([server1], 
   'select schema1.table1.col1, schema1.table1.col2 from schema1.table1 where schema1.table1.col1 exists 
   (select * from openquery([server2],'select schema2.table2.col1 from schema2.table2')')

Any help would be highly appreciated.

Thanks a lot in advance.

Community
  • 1
  • 1
Mekala
  • 21
  • 3
  • Assuming this is SQL Server? – gvee Dec 20 '13 at 14:43
  • What database? As ways to do things frequently vary between DBMSs, it helps if you add the proper DBMS (SQL Server, MySQL, Oracle, or whatever) tags to your question. – Ken White Dec 20 '13 at 15:19
  • Yes Ken White. I should have been bit clear on the database part. Both are DB2 servers and I have these servers added as linked servers. I am trying to execute this query in SSMS. – Mekala Dec 23 '13 at 06:11

1 Answers1

0

I literally just put this together to query between a local table and 1 linked db table. Intellisense may not 'like' the linked server table, but it does work:

Select GlobalEntityID, LicenseNumber, IssueDate, ExpireDate, BusinessLicenseClosedDate, bl.Issued, bl.Expire, bl.Cease
From COLICENSECERTIFICATION lic 
Inner Join 
    [LinkedServer].[DB].[schema].[blAttribs] bl on lic.LICENSENUMBER = bl.Account_key
Order by LicenseNumber;
go

You can put your 'Where' clause in there to match any other conditions as well or add another Inner Join to add another linked server table to the query.

JFV
  • 1,803
  • 6
  • 24
  • 38
  • I am using SQL Server 2012 for this query, but it should work for 2005 on up. – JFV Dec 20 '13 at 15:00
  • Thanks JFV. I could access one linked server at a time but couldnt when I want to retrieve data from two different linked servers.To add here, both are DB2 linked servers – Mekala Dec 23 '13 at 06:14
  • Ok, I don't have any DB2 Servers to test with. Good luck! – JFV Dec 24 '13 at 16:31
  • Thanks JFV. I got a solution for this and the query is.. select * from openquery([Server name],'select * from schema.table') where exists(select * from openquery([server2],'select * from schema.table')) – Mekala Dec 27 '13 at 10:16