Openquery statement in SQL Server

Question

I am fairly new to SQL, and I am hoping someone can help me with a problem I'm having. I haven't been able to find any answers helping me figure out this exact problem.

I have two tables in two SQL Server databases on two different servers that I want to compare using the column ItemID. I want to find records from Table1 that have an ItemID that does not exist in Table2 and insert those into a table variable. I have the following code:

--Create table variable to hold query results 
DECLARE @ItemIDTable TABLE 
  ( 
     [itemid][NVARCHAR](20) NULL 
  ); 

--Query data and insert results into table variable  
INSERT INTO @ItemIDTable 
            ([itemid]) 
SELECT a.[itemid] 
FROM   database1.dbo.table1 a 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   [Database2].[dbo].[table2] 
                   WHERE  a.itemid = [Database2].[dbo].[table2].[itemid]) 
ORDER  BY itemid

This works on a test server where the two databases are on the same server, but not in real life where they are on different servers. I tried the following using OPENQUERY, but I know I haven't got it quite right.

--Create table variable to hold query results
DECLARE @ItemIDTable TABLE
    (
    [ItemID][nvarchar](20) NULL
    );

--Query data and insert results into table variable 
INSERT INTO @ItemIDTable
    ([ItemID])
    SELECT a.[ItemID]
    FROM  Database1.dbo.Table1 a 
    WHERE NOT EXISTS (SELECT 1 
                      FROM OPENQUERY([Server2], SELECT * FROM [Database2].[dbo].[Table2]') 
                      WHERE a.ItemID = [Database2].[dbo].[Table2].[ItemID])
    ORDER BY ItemID

I'm pretty sure I need to do something in the WHERE clause, where I have the two databases on two servers, I'm just not quite sure how to structure it. Could anyone help?

score 2 · Answer 1 · answered Oct 14 '14 at 16:34

Openquery works like this:

select * 
from openquery
(LINKED_SERVER_NAME,
'select query goes here'
)

Note that the sql portion is single quoted. That means you might have to quote the quotes if necessary. For example:

select * 
from openquery
(LINKED_SERVER_NAME,
'
select SomeTextField
from SomeTable
where SomeDateField = ''20141014''
'
)

score 2 · Accepted Answer · answered Oct 14 '14 at 16:34

2

You can't create an OPENQUERY that is correlated to an outer query. You could populate a temp table with the results of an OPENQUERY and do your WHERE NOT EXISTS against the temp table, or you might want to look into Synonyms.

answered Oct 14 '14 at 16:34

Tab Alleman

31,483
7
36
52

Thank you for your answer. I used your suggestion to populate a temp table with the OPENQUERY results, and it worked perfectly. – lunderkl041 Oct 14 '14 at 16:53

Openquery statement in SQL Server

2 Answers2