How to join linked server table and sql server table while using openquery

sql-server openquery
Muthukumar picture Muthukumar · Sep 5, 2012 · Viewed 51.4k times · Source

I use openquery syntax to read the data from a linked server. 

SELECT * FROM OPENQUERY(LinkServer, 'SELECT * FROM Product')

I want to join this link server table with an Sql server table to get my final results. For now I do it by, having a temp table.

SELECT * 
INTO #Temp_Products
FROM OPENQUERY(TREPO, 'SELECT ID, Name FROM Products')

SELECT * FROM #TEMP_PRODUCTS A
INNER JOIN ORDERED_PRODUCTS B
ON A.ID = B.ID

But as the link server product table contains huge records, it takes time to get filled into the temp table. So I think instead of pulling all product information, If I join both the tables before hand, it could increase the performance.

Can this be done.? Can someone help?

Answer

EastOfJupiter picture EastOfJupiter · Sep 5, 2012

I don't have the ability to test this, but it does offer an opportunity to bypass the #tempTable option by directly joining to the remote server (if such connection is possible)

SELECT  A.* 
  FROM  OPENQUERY(TREPO, 'SELECT ID, Name FROM Products') A
 INNER 
  JOIN  ORDERED_PRODUCTS B
    ON  A.ID = B.ID

Here is a link to some information about linked server queries, and some pitfalls that can be encountered: http://sqlblog.com/blogs/linchi_shea/archive/2009/11/06/bad-database-practices-abusing-linked-servers.aspx

Related questions

Execute stored proc with OPENQUERY

I have SQL Server 2008 with a linked Sybase server and I am trying to execute a stored procedure on the Sybase server using OPENQUERY. If I have a stored proc that doesn't take parameters it succeeds fine. If I have …

Read More
Querying an Oracle database from SQL Server

I have an Oracle 11g XE database that I would like to transfer into SQL Server Express 2005. At first I thought I'd just generate the tables in Oracle as SQL, manipulate the data formats, and run the query in SQL …

Read More
What is a "INSERT IGNORE" equivalent in MS SQL Server?

I am trying to insert records into MySQL database from a MS SQL Server using the "OPENQUERY" but what I am trying to do is ignore the duplicate keys messages. so when the query run into a duplicate then ignore …

Read More