Insert dynamic query results into un-defined temp table?

Question

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)

The attempt 1 should work, but the problem of course is that the table will be dropped when the execute ends -- and the insert into ... execute works only with already defined tables — James Z
– James Z, Commented Oct 27, 2015 at 16:44

Lukasz Szozda · Accepted Answer · 2015-10-27 16:55:57Z

2

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.

edited Oct 27, 2015 at 16:55

answered Oct 27, 2015 at 16:47

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Z Over a year ago

Just remember that global temporary tables are shared by all sessions, including the data

Collectives™ on Stack Overflow

Insert dynamic query results into un-defined temp table?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related