CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ab11
ab11

Reputation: 20090

Insert dynamic query results into un-defined temp table?

I'm defining a long dynamic query and I'd like to insert it's results into a table. However, I'd prefer not to define the table first. Is this possible?

The query works correctly, I see the expected results if I run this:

declare @query VARCHAR(MAX)
@query = 'SELECT
               --a bunch of stuff involving joins and pivots and such
         '
execute (@query)

But neither of these attempts to select into an un-defined temp table work:

--attempt 1
    declare @query VARCHAR(MAX)
    @query = 'SELECT * INTO #T1 (
                SELECT
                   --a bunch of stuff involving joins and pivots and such
                )
             '
    execute (@query)

--attempt 2
    declare @query VARCHAR(MAX)
    @query = 'SELECT
                   --a bunch of stuff involving joins and pivots and such
             '
    execute (@query)
    select * INTO #T1  execute (@query)

Upvotes: 2

Views: 69

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

One workaround is to use global temp table:

SET @query = 'SELECT * INTO ##T1 FROM (
                SELECT
                   --a bunch of stuff involving joins and pivots and such
                )';

EXECUTE(@query);

SELECT *    -- reasign to local temp table to avoid reserving global ##T1 name
INTO #T1    -- if needed you can skip this part and work only on global table
FROM ##T1;

DROP TABLE ##T1;

SELECT *
FROM #T1;

LiveDemo

The normal local temporary table won't work, because Dynamic SQL creates new context. The table is in that context and will cease to exist when code is executed, so you cannot use it outside Dynamic-SQL.

Upvotes: 2

Related Questions