Using Temp Table created by using dynamic query in Joins

Question

I have a dynamic query which gets certain records from another database (database server and database name are variables, hence used dynamic query).

Below is the query

DECLARE @SQLString NVARCHAR(1000)      
set @SQLString='
select distinct(select distinct
(
select * from
(
    ------- Inner query (It is more complex than this)

    select lAccountId, sAccountName 
    from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccDetails  
    where lAccountId = 10  
    union  
    select lAccountId, sAccountName 
    from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccHistoryDetails  
    where lAccountId = 10 
) A
    for xml raw(''Account''), ROOT(''Accounts''), ELEMENTS 
)) as AccXmlValue,
lAccountId as AccountId      
into       
 #tmpAccDetails
from       
 AccountDetails      
where       
 AccountDetails.laccountID in (''10,11'')'     

EXECUTE (@SQLString)    

----- This is the final SQL statement (It is more complex than this)

select * from 
MainAccTable M
inner join #tmpAccDetails tmp on M.lAccountId = tmp.AccountId 

I want to use the #tmpAccDetails in join with MainAccTable.

1. How can I achieve this, as the temp table will not be in scope outside the dynamic SQL?
2. Using Global Temp table solves this, but will it be a good idea to use it in this scenario?

My question is similar to this Question here, except for the fact that I will have to use the #tmpAccDetails table in join, rather than selecting the data from this at one go.

Any help will be appreciated. Thanks.

Answer 1

I have myself solved the question and posting the answer here, so that others may benefit from the same.

DECLARE @SQLString NVARCHAR(MAX)      
set @SQLString='
select distinct(select distinct
(
select * from
(
------- Inner query (It is more complex than this)

select lAccountId, sAccountName 
from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccDetails  
where lAccountId = 10  
union  
select lAccountId, sAccountName 
from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccHistoryDetails  
where lAccountId = 10 
) A
for xml raw(''Account''), ROOT(''Accounts''), ELEMENTS 
)) as AccXmlValue,
lAccountId as AccountId      
from       
 '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccountDetails     
where       
 laccountID in (''10,11'')' 

---- Create temp table here
CREATE TABLE #tmpAccDetails 
(
AccXmlValue NVarchar(max),
AccountId int 
);

---- Insert into temp table here
INSERT INTO #tmpAccDetails EXECUTE (@SQLString)

---- Select from temp table here
Select * from 
MainAccTable M
inner join #tmpAccDetails tmp on M.lAccountId = tmp.AccountId 

Answer 2

Have you considered "OPENDATASOURCE" to access data from another database ? https://msdn.microsoft.com/fr-fr/library/ms179856.aspx

You can join tables from different databases with this method

    SELECT *
    FROM MainAccTable M
    INNER JOIN OPENDATASOURCE ('SQLOLEDB', 'Data Source=@myInstance;User ID=@myUserName;Password=@myPassword).XXX.dbo.AccountId AS tmp 
ON tmp.AccountId = M.lAccountId 

Answer 3

BEGIN TRAN  
    DECLARE @SQLString NVARCHAR(1000)      

      CREATE TABLE #tmpAccDetails
      (lAccountId int, 
       sAccountName  NVArchar(100)
      );


      set @SQLString=' INSERT INTO  #tmpAccDetails

      select distinct(select distinct
      (
      select * from
      (
          ------- Inner query (It is more complex than this)

          select lAccountId, sAccountName 
          from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccDetails  
          where lAccountId = 10  
          union  
          select lAccountId, sAccountName 
          from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccHistoryDetails  
          where lAccountId = 10 
      ) A
          for xml raw(''Account''), ROOT(''Accounts''), ELEMENTS 
      )) as AccXmlValue,
      lAccountId as AccountId      
      into       
       #tmpAccDetails
      from       
       AccountDetails      
      where       
       AccountDetails.laccountID in (''10,11'')'     

      EXECUTE (@SQLString)    

      Select * from 
      MainAccTable M
      inner join #tmpAccDetails tmp on M.lAccountId = tmp.AccountId 


      RollBAck Tran

Answer 4

Just change the order of the statements, like this:

DECLARE @SQLString NVARCHAR(1000)   

set @SQLString='
select distinct(select distinct
(
select * from
(
    ------- Inner query (It is more complex than this)

    select lAccountId, sAccountName 
    from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccDetails  
    where lAccountId = 10  
    union  
    select lAccountId, sAccountName 
    from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccHistoryDetails  
    where lAccountId = 10 
) A
    for xml raw(''Account''), ROOT(''Accounts''), ELEMENTS 
)) as AccXmlValue,
lAccountId as AccountId      
into       
 #tmpAccDetails
from       
 AccountDetails      
where       
 AccountDetails.laccountID in (''10,11'')'     


CREATE TABLE #tmpAccDetails
(lAccountId int, 
 sAccountName  NVArchar(100)
);

INSERT INTO #tmpAccDetails
EXEC sp_executesql @sSQL    

Select * from 
MainAccTable M
inner join #tmpAccDetails tmp on M.lAccountId = tmp.AccountId 

Answer 5

I think you'll be OK if you create the temp table first....

Eg....

DECLARE @SQLString NVARCHAR(1000)      

CREATE TABLE #tmpAccDetails
(lAccountId int, 
 sAccountName  NVArchar(100)
);


set @SQLString='
select distinct(select distinct
(
select * from
(
    ------- Inner query (It is more complex than this)

    select lAccountId, sAccountName 
    from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccDetails  
    where lAccountId = 10  
    union  
    select lAccountId, sAccountName 
    from '+@DatabaseServer+'.'+@DatabaseName+'.dbo.AccHistoryDetails  
    where lAccountId = 10 
) A
    for xml raw(''Account''), ROOT(''Accounts''), ELEMENTS 
)) as AccXmlValue,
lAccountId as AccountId      
into       
 #tmpAccDetails
from       
 AccountDetails      
where       
 AccountDetails.laccountID in (''10,11'')'     

EXECUTE (@SQLString)    

Select * from 
MainAccTable M
inner join #tmpAccDetails tmp on M.lAccountId = tmp.AccountId