Create a temp table from a dynamic query

Question

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.

Answer

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.

Answer

Hi @Ali Ahad ,

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.

Answer

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.

Answer

Hi @Ali Ahad ，

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.

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.

Answer

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.

Share via

Create a temp table from a dynamic query

5 answers

Your answer