Create a temp table from a dynamic query

Question

Create a temp table from a dynamic query

aliahad

SSC Enthusiast

Points: 115
More actions
May 4, 2021 at 6:49 pm

#3879282
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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24689 More actions · Answer 1

Just to confirm, your query is in the logical format of:

DECLARE @dynSQL NVARCHAR(MAX)
SELECT @dynSQL = 'SELECT 1 AS [result]'

EXEC (@dynSQL)

And you are wanting to insert the results of EXEC into the table #TEST. If so, the following should work:

CREATE TABLE #TEST (result INT)
DECLARE @dynSQL NVARCHAR(MAX)
SELECT @dynSQL = 'SELECT 1 AS [result]'

INSERT INTO #TEST
EXEC (@dynSQL)

SELECT * FROM #TEST

DROP TABLE #TEST

If the above example is too simplistic or completely off topic from what you are wanting, can you post your query (or an example query) that meets your needs?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

aliahad SSC Enthusiast Points: 115 More actions · Answer 2

See my code

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

I am using a CTE statement to get my final output but I want the output in a temp table so I can use with other database tables.

Thanks,

Ali.