Dynamic Temp Table Pains

Tinker Toy

Let’s say you have dynamic SQL that selects different different data based on some conditions.

Let’s also say that data needs to end up in a temp table.

Your options officially suck.

If you create the table outside dynamic SQL, you need to know which columns to use, and how many, to insert into the table.

You can’t do SELECT…INTO with an EXEC.

If you create the table inside dynamic SQL, you can’t use it outside the dynamic SQL.

But…

Altered Images

There’s a fun function in SQL Server 2012+, dm_exec_describe_first_result_set.

People mainly use it for stored procedures (I think?), but it can also work like this:

The results for the Users table look like this:

For you must

Don’t Judge Me

The best way I’ve found to do this is to use that output to generate an ALTER TABLE to add the correct columns and data types.

Here’s a dummy stored procedure that does it:

I can execute it for either Users or Posts, and get back the results I want.

So yeah, this is generally a pretty weird requirement.

It might even qualify as Bad Idea Spandex™

Thanks for reading!

1 thought on “Dynamic Temp Table Pains”

  1. I’ve done similar for dynamic pivots that I want to re-query.

    1) Start with a long table (probably something like similar to EAV)
    2) Create a temp table with a key column(s) (use whatever the pivot would be grouped by)
    3) Get each attribute from the long table and create an ALTER statement to add columns by those names
    4) Create a dynamic pivot insert into the wide temp table
    5) Query the resulting table

Leave a Reply

Your email address will not be published. Required fields are marked *