May 4, 2021 at 6:49 pm
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.
May 4, 2021 at 9:42 pm
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.
May 4, 2021 at 10:37 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply