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.

How can I achieve this, as the temp table will not be in scope outside the dynamic SQL?
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.

AntDC · Accepted Answer · 2017-02-01 10:13:55Z

1

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

answered Feb 1, 2017 at 10:13

AntDC

1,93715 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Senjuti Mahapatra Over a year ago

No, this does not work. The data does not get inserted into the temp table this way.

Senjuti Mahapatra Over a year ago

It does not give any error, it returns empty result.

AntDC Over a year ago

Suggesting it is inserting somewhere or the dynamic sql is not returning rows. You have got my curiosity on this one........I'm going to experiment

Senjuti Mahapatra Over a year ago

The temp table goes out of scope after the dynamic query, I guess. Do you know any other way of achieving this functionality?

Senjuti Mahapatra Over a year ago

I have solved the question myself and posted the answer. +1 for your time and effort :)

|

ser_nicky · Accepted Answer · 2017-02-01 11:01:06Z

1

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

answered Feb 1, 2017 at 11:01

ser_nicky

3561 silver badge5 bronze badges

4 Comments

Senjuti Mahapatra Over a year ago

What is @sSQL here?

Senjuti Mahapatra Over a year ago

@ser_nicky It works if I run this as a query, does not work if it is put inside a stored procedure.

ser_nicky Over a year ago

@Senjuti Mahapatra - Oh, you didn't mention that this portion of code should be run within the stored procedure. Since you haven't provided SP details, please take a look at: social.msdn.microsoft.com/Forums/sqlserver/en-US/…

Senjuti Mahapatra Over a year ago

@ser_nicky: Your answer was almost correct and helped me to solve my question. I have solved the question myself and posted the answer. +1 for your time and effort.

Alfaiz Ahmed · Accepted Answer · 2017-02-01 11:17:23Z

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

abootis · Accepted Answer · 2017-02-01 13:42:22Z

0

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

answered Feb 1, 2017 at 13:42

abootis

163 bronze badges

Comments

Senjuti Mahapatra · Accepted Answer · 2017-02-02 05:28:06Z

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

Collectives™ on Stack Overflow

Using Temp Table created by using dynamic query in Joins

5 Answers 5

6 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related