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.

  • 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?

  • 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 3 (of 3 total)

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