0

So I have this query I use to deal with a problem between my SQL Server and a linked Oracle server. Currently there is no better solution and this solution is quick and efficient but I just don't like the fact that I have to create a temp table. I feel like the results from exec() should be able to function as a sub query but it cannot.

My question is why? If I run exec(@OPENQUERYFULL) I get a return of all the expected data.

At the same time I can also do INSERT #myTemp exec (@OPENQUERYFULL) and that load the same data to a temp table. (Can also do this to a perm table).

So why can I not simple do something like this:

SELECT LT.U_ID
      ,LT.B_ID
      ,LT.T_NBR
      ,TMP.C_NBR
      ,TMP.A_ID
      ,TMP.L_NBR
FROM LOCAL_TABLE LT
LEFT OUTER JOIN exec(@OPENQUERYFULL) TMP
    ON LT.C_NBR = TMP.C_NBR

Original working solution with temp table:

DECLARE @OPENQUERYINTRO nvarchar(4000)
DECLARE @OPENQUERYBODY nvarchar(4000)
DECLARE @SQLSERVERBODY nvarchar(4000)
DECLARE @OPENQUERYFULL nvarchar(4000)

SET @OPENQUERYINTRO = 'SELECT * FROM OPENQUERY(LINKED_SERVER,'''
SET @OPENQUERYBODY = 'SELECT C_NBR, A_ID, L_NBR FROM TABLE_NAME WHERE C_NBR IN ('
SET @SQLSERVERBODY = (
SELECT RTRIM(LTRIM(STUFF((
    SELECT distinct ',' + STR([C_NBR])
        FROM LOCAL_TABLE FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''))))
SET @OPENQUERYFULL = @OPENQUERYINTRO + @OPENQUERYBODY + @SQLSERVERBODY + ')'')'


IF OBJECT_ID('tempdb..#myTemp', 'U') IS NOT NULL
    DROP TABLE #myTemp;

CREATE TABLE #myTemp(C_NBR varchar(10), A_ID varchar(20), L_NBR varchar(20));

INSERT #myTemp exec (@OPENQUERYFULL);

SELECT LT.U_ID
      ,LT.B_ID
      ,LT.T_NBR
      ,TMP.C_NBR
      ,TMP.A_ID
      ,TMP.L_NBR
FROM #myTemp TMP
LEFT OUTER JOIN LOCAL_TABLE LT
    ON LT.C_NBR = TMP.C_NBR
Mike - SMT
  • 14,784
  • 4
  • 35
  • 79

1 Answers1

0

I understand the problem. This is how I fixed it on my side: I wrapped the openquery in a view:

CREATE VIEW [schema].[v_view]
AS
        SELECT * FROM openquery([LinkedServer], '
        SELECT  *
        FROM    [Server].[schema].[Object]  
        ');

and they I can do 

Select * From [schema].[v_view]

and all the joins I want

Hope it helps

zip
  • 3,938
  • 2
  • 11
  • 19
  • Hum. Well I don't actually know if a view is better than a table. But it is worth investigating. – Mike - SMT Nov 20 '19 at 21:31
  • You don't even need to create a view. See this for an example: https://stackoverflow.com/a/12285573/6305294 – Alex Nov 21 '19 at 00:07