question

AliAhad-7499 avatar image
0 Votes"
AliAhad-7499 asked EchoLiu-msft answered

Create a temp table from a dynamic query

Hello,

I created a dynamic query and getting the expected results, but I want to get my results in a temp table, I have tired using global/local temp tables but I am getting the error message below:

Msg 208, Level 16, State 0, Line 3
Invalid object name '#TEST'.

Any help will be appreciated.

Thanks,

Ali.

sql-server-transact-sql
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


The next examples seem to work:

 create table #TEST (x int )
    
 declare @dynamic_query as varchar(max)
    
 set @dynamic_query = 'insert into #TEST select 1 union select 2 union select 3'
    
 exec (@dynamic_query)
    
 select * from #TEST

or:

 create table #TEST (x int )
    
 declare @dynamic_query as varchar(max)
    
 set @dynamic_query = 'select 1 union select 2 union select 3'
    
 insert into #TEST 
 exec (@dynamic_query)
    
 select * from #TEST

Therefore, you should show your example that does not work.


0 Votes 0 ·

I am using a CTE statement in my dynamic query:

 SET @SQL = N' ; 
    
 WITH CTE AS 
 (
    
         SELECT
 --mycode
 )
    
    
 --insert into #TEST
    
 SELECT
     --RowNo,
     MRN,
     VISIT_ID,
     PATIENT_NAME,
     ADMIT_DATE,
     DISCHARGE_DATE,
     PATIENT_TYPE,
     ADMIT_TYPE,
     ADMIT_SERVICE' + @SQL + N'
    
    
    
    
 FROM 
     CTE
    
    
 GROUP BY
     --RowNo,
     MRN,
     VISIT_ID,
     PATIENT_NAME,
     ADMIT_DATE,
     DISCHARGE_DATE,
     PATIENT_TYPE,
     ADMIT_TYPE,
     ADMIT_SERVICE
 ORDER BY 
     MRN'
     ;
    
 PRINT @SQL;
    
 EXECUTE (@SQL);
0 Votes 0 ·

The next example creates the target table and executes a dynamic query successfully:

 create table #TEST (x int )
    
 declare @SQL as nvarchar(max)
    
 set @SQL = N'
 ;
 with CTE as
 (
     select 1 as x
     union select 2
     union select 3
    
 )
 insert into #TEST
 select * from CTE
 '
 exec (@SQL)
    
 select * from #TEST

You can also consider the 'insert into ... exec ...' method.

Or maybe you want to create the table automatically without 'create table' statement?

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered EchoLiu-msft commented

We don't see your code, but the common error is to create the temp table inside the dynamic SQL. The dynamic SQL is a nameless stored procedure of its own, and a temp table created in a stored procedure is dropped automatically when the SP exists. And that includes nameless SPs created with dynamic SQL.

This is why Viorel has been showing you examples where the temp table is created before the dynamic SQL.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am not able to upload the code since it only allow me 1000 characters

0 Votes 0 ·

The number of characters in the comment is limited, and you can post it in the answer.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @AliAhad-7499,

Welcome to the microsoft TSQL Q&A forum!

First, you need to create a temporary table, and then the table will be available in dynamic SQL.

Please refer:

 CREATE PROC pro1 @var VARCHAR(100)
 AS
 EXEC (@var)
 GO
    
 CREATE TABLE #temp (id INT)
    
 EXEC pro1 'insert #temp values(1)'
    
 SELECT *
 FROM #temp

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AliAhad-7499 avatar image
0 Votes"
AliAhad-7499 answered ErlandSommarskog commented
 DECLARE @MAXCOUNT INT;
    
    
    
 IF OBJECT_ID('tempdb..#VST_DIA') IS NOT NULL
     DROP TABLE #VST_DIA
    
 SELECT
 vst_int_id,
 icd9_code_fmt
 --present_at_admit_fg
    
 INTO
 #VST_DIA
    
 FROM
 TPM318_VISIT_DIAGNOSIS VST_DIA (NOLOCK)
 INNER JOIN TSM910_ICD9_REF ICD (NOLOCK)
 ON ICD.icd9_int_id = VST_DIA.icd9_int_id
 --INNER JOIN TPM300_PAT_VISIT VST (NOLOCK)
 --ON VST.vst_int_id = VST_DIA.vst_int_id
    
 WHERE
 1=1
 AND VST_DIA.row_sta_cd = 'A'
 AND VST_DIA.ICD9_diag_ty IN ('P', 'S')
 AND ICD.icd9_code_fmt IN (
 'O10.03',
 'O10.13',
 'O10.23',
 'O10.33',
 'O10.43',
 'O10.93',
 'O11.5',
 'O12.05',
 'O12.15',
 'O12.25',
 'O13.5',
 'O14.05',
 'O14.15',
 'O14.25',
 'O14.95',
 'O15.2',
 'O16.5',
 'O24.03',
 'O24.13',
 'O24.33',
 'O24.83',
 'O24.93',
 'O25.3',
 'O26.63',
 'O26.73',
 'O98.03',
 'O98.13',
 'O98.23',
 'O98.33',
 'O98.43',
 'O98.53',
 'O98.63',
 'O98.73',
 'O98.83',
 'O98.93',
 'O99.03',
 'O99.13',
 'O99.215',
 'O99.285',
 'O99.315',
 'O99.325',
 'O99.325',
 'O99.335',
 'O99.345',
 'O99.355',
 'O99.43',
 'O99.53',
 'O99.63',
 'O99.73',
 'O99.815',
 'O99.825',
 'O99.835',
 'O99.845',
 'O99.893',
 'O9A.13',
 'O9A.23',
 'O9A.33',
 'O9A.43',
 'O9A.53'
 )
 --present_at_admit_fg ='Y'
    
 --select * from #VST_DIA
    
 SELECT @MAXCOUNT = MAX(CNT)
 FROM (
       SELECT 
       vst_int_id,
       COUNT(icd9_code_fmt) AS CNT
       FROM
       #VST_DIA
       GROUP BY vst_int_id
       ) X;
    
 DECLARE @SQL NVARCHAR(MAX)
         ,@i INT;
    
 SET @i = 0;
 SET @SQL = '';
    
 WHILE @i < @MAXCOUNT
 BEGIN
     SET @i = @i + 1;
     SET    @SQL = @SQL + ',
     MAX(CASE WHEN RowNo = ' + CAST(@i as nvarchar(10)) + ' THEN icd9_code_fmt END) AS ICD_CODE_' + CAST(@i as nvarchar(10));
 END
    
    
    
 SET @SQL = N' ; 
    
 --IF OBJECT_ID(''tempdb..#TEST'') IS NOT NULL
 ----BEGIN
 --    DROP TABLE #TEST
    
 WITH CTE AS 
 (
    
         SELECT
             LTRIM(RTRIM(TPM300.med_rec_no)) AS MRN,
             LTRIM(RTRIM(TPM300.vst_ext_id)) AS VISIT_ID,
             LTRIM(RTRIM(dbo.ufn_get_person_full_name(TPM300.psn_int_id))) AS PATIENT_NAME,
             FORMAT(TPM300.adm_ts, ''MM/dd/yyyy'')  + '' ''+ FORMAT(TPM300.adm_ts, ''HH:mm'') as ADMIT_DATE,
             FORMAT(TPM300.dschrg_ts, ''MM/dd/yyyy'')  + '' ''+ FORMAT(TPM300.dschrg_ts, ''HH:mm'') as DISCHARGE_DATE,
             LTRIM(RTRIM(dbo.fn_get_cod_ds(TPM300.pat_ty))) AS PATIENT_TYPE,
             LTRIM(RTRIM(dbo.fn_get_cod_ds(TPM300.adm_ty))) AS ADMIT_TYPE,
             LTRIM(RTRIM(dbo.fn_get_cod_ds(TPM300.adm_srv_cd))) AS ADMIT_SERVICE, 
             #VST_DIA.vst_int_id, 
             #VST_DIA.icd9_code_fmt,
             ROW_NUMBER() OVER (PARTITION BY TPM300.med_rec_no--#VST_DIA.vst_int_id 
             ORDER BY TPM300.med_rec_no, TPM300.dschrg_ts
             --#VST_DIA.icd9_code_fmt
             ) AS RowNo
            
    
         FROM 
             TPM300_PAT_VISIT TPM300 (NOLOCK)
             INNER JOIN #VST_DIA (NOLOCK)
             ON #VST_DIA.vst_int_id = TPM300.vst_int_id
                
    
         WHERE
             1=1
 --            AND 
 --            LTRIM(RTRIM(TPM300.vst_ext_id)) = ''200730268''
 )
    
    
 --insert into #TEST
    
    
 SELECT
     --RowNo,
     MRN,
     VISIT_ID,
     PATIENT_NAME,
     ADMIT_DATE,
     DISCHARGE_DATE,
     PATIENT_TYPE,
     ADMIT_TYPE,
     ADMIT_SERVICE'     + @SQL + N'
    
    
    
    
 FROM 
     CTE
    
    
 GROUP BY
     --RowNo,
     MRN,
     VISIT_ID,
     PATIENT_NAME,
     ADMIT_DATE,
     DISCHARGE_DATE,
     PATIENT_TYPE,
     ADMIT_TYPE,
     ADMIT_SERVICE
 ORDER BY 
     MRN'
     ;
    
 PRINT @SQL;
    
 EXECUTE (@SQL);
    
    
    
 --select *
 --from #TEST

Here is my code and I am getting the results, but I want the results in a temp table so I can join with other tables.

Thanks,
Ali.

· 11
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what's wrong with creating the temp table before you execute the dynamic SQL?

0 Votes 0 ·

and how would I insert the data from dynamic SQL

0 Votes 0 ·

The INSERT statement is already there it seems. But you have commented it out.

You can also say INSERT #temp EXEC(@sql) if you prefer.

0 Votes 0 ·
Show more comments
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Hi @AliAhad-7499

There seems to be a problem with your syntax for inserting data into the temporary table:

     INSERT INTO #TEST
     SELECT col1,col2,...
     FROM sourcetable

When using the above statement, #TEST table needs to be created in advance. That is, #TEST should be created before the INSERT INTO statement above. You didn't create it in advance, so an error was returned.
94120-image.png

You can use:

 SELECT col1,col2,...INTO #test
 FROM sourcetable

This eliminates the need to create temporary tables in advance.In addition, it seems that you need to use the global temporary table ##test.


Please refer to the modified code:

 DECLARE @MAXCOUNT INT;
 DROP TABLE IF EXISTS #VST_DIA
    
 SELECT vst_int_id,icd9_code_fmt
  --present_at_admit_fg
 INTO #VST_DIA
 FROM TPM318_VISIT_DIAGNOSIS VST_DIA (NOLOCK)
 INNER JOIN TSM910_ICD9_REF ICD (NOLOCK)
 ON ICD.icd9_int_id = VST_DIA.icd9_int_id
 --INNER JOIN TPM300_PAT_VISIT VST (NOLOCK)
 --ON VST.vst_int_id = VST_DIA.vst_int_id
 WHERE 1=1 AND VST_DIA.row_sta_cd = 'A' AND VST_DIA.ICD9_diag_ty IN ('P', 'S')
  AND ICD.icd9_code_fmt IN ('O10.03','O10.13','O10.23','O10.33','O10.43',
  'O10.93','O11.5','O12.05','O12.15','O12.25','O13.5','O14.05','O14.15',
  'O14.25','O14.95','O15.2','O16.5','O24.03','O24.13','O24.33','O24.83',
  'O24.93','O25.3','O26.63','O26.73','O98.03','O98.13','O98.23','O98.33',
  'O98.43','O98.53','O98.63','O98.73','O98.83','O98.93','O99.03','O99.13',
  'O99.215','O99.285','O99.315','O99.325','O99.325','O99.335','O99.345',
  'O99.355','O99.43','O99.53','O99.63','O99.73','O99.815','O99.825',
  'O99.835','O99.845','O99.893','O9A.13','O9A.23','O9A.33','O9A.43','O9A.53')
  --present_at_admit_fg ='Y'
        
  --select * from #VST_DIA
        
 SELECT @MAXCOUNT = MAX(CNT)
 FROM (
        SELECT vst_int_id,
        COUNT(icd9_code_fmt) AS CNT
        FROM #VST_DIA
        GROUP BY vst_int_id) X;
        
 DECLARE @SQL NVARCHAR(MAX)
          ,@i INT;
 SET @i = 0;
 SET @SQL = '';
        
 WHILE @i < @MAXCOUNT
 BEGIN
      SET @i = @i + 1;
      SET    @SQL = @SQL + ',
      MAX(CASE WHEN RowNo = ' + CAST(@i as nvarchar(10)) + ' THEN icd9_code_fmt END) AS ICD_CODE_' + CAST(@i as nvarchar(10));
 END
        
 SET @SQL = N'DROP TABLE IF EXISTS ##test
  ;WITH cte AS 
  (SELECT LTRIM(RTRIM(TPM300.med_rec_no)) AS MRN,
          LTRIM(RTRIM(TPM300.vst_ext_id)) AS VISIT_ID,
          LTRIM(RTRIM(dbo.ufn_get_person_full_name(TPM300.psn_int_id))) AS PATIENT_NAME,
          FORMAT(TPM300.adm_ts, ''MM/dd/yyyy'')  + '' ''+ FORMAT(TPM300.adm_ts, ''HH:mm'') as ADMIT_DATE,
          FORMAT(TPM300.dschrg_ts, ''MM/dd/yyyy'')  + '' ''+ FORMAT(TPM300.dschrg_ts, ''HH:mm'') as DISCHARGE_DATE,
          LTRIM(RTRIM(dbo.fn_get_cod_ds(TPM300.pat_ty))) AS PATIENT_TYPE,
          LTRIM(RTRIM(dbo.fn_get_cod_ds(TPM300.adm_ty))) AS ADMIT_TYPE,
          LTRIM(RTRIM(dbo.fn_get_cod_ds(TPM300.adm_srv_cd))) AS ADMIT_SERVICE, 
          #VST_DIA.vst_int_id, 
          #VST_DIA.icd9_code_fmt,
          ROW_NUMBER() OVER (PARTITION BY TPM300.med_rec_no--#VST_DIA.vst_int_id 
          ORDER BY TPM300.med_rec_no, TPM300.dschrg_ts
          --#VST_DIA.icd9_code_fmt) AS RowNo
 FROM TPM300_PAT_VISIT TPM300 (NOLOCK)
 INNER JOIN #VST_DIA (NOLOCK)
 ON #VST_DIA.vst_int_id = TPM300.vst_int_id
 WHERE 1=1
  --AND 
  --LTRIM(RTRIM(TPM300.vst_ext_id)) = ''200730268'')
        
        
  --insert into #test
        
 SELECT --RowNo,MRN,VISIT_ID,PATIENT_NAME,ADMIT_DATE,
      DISCHARGE_DATE,PATIENT_TYPE,ADMIT_TYPE,ADMIT_SERVICE'     + @SQL + N'
 INTO ##test
 FROM cte
 GROUP BY --RowNo,
      MRN,VISIT_ID,PATIENT_NAME,ADMIT_DATE,
      DISCHARGE_DATE,PATIENT_TYPE,
      ADMIT_TYPE,ADMIT_SERVICE
 ORDER BY MRN';
        
 PRINT @SQL;
        
 EXECUTE (@SQL);
        
 SELECT  * FROM ##test

The following are examples of two correct methods and one incorrect method of inserting the results of dynamic SQL into a temporary table:

 --× Msg 208, Level 16, State 0, Line 7 Invalid object name '#test'.
 Declare @sql as varchar(100)
 Drop Table If exists #TEST
 Set @sql = 'Select GetDate() As TheDate Into #test'
 Execute(@sql)
    
 Select * From #test
    
    
 --√ #test
 Declare @sql as varchar(100)
 Drop Table If exists #TEST
 Create Table #test (TheDate DateTime)
 Set @sql = 'Insert Into #test Select GetDate() As TheDate'
 Execute(@sql)
    
 Select *From #test
    
 --√ ##test
 Declare @sql as varchar(100)
 Drop Table If exists ##test
 Set @sql = 'Select GetDate() As TheDate Into ##test'
 Execute(@sql)
    
 Select * From ##test

Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.






image.png (6.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried your code too I still the get the invalid object error and also incorrect syntax error near from CTE statement

0 Votes 0 ·

Glad to see that your problem has been resolved. Because the code pattern in the answer is what I tested with my data, and it returned the result I expected. So I have been struggling with why your code still returns an error.

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.