Insert dynamic query result set into temporary table on the fly

Question

I need to insert the result set from dynamic query

EXEC sp_executesql  @Query

into a temp table. this temp table should be created on the fly, instead of already creating it, because the columns from the dynamic query are not constant. please guide me

Edit: why i need to insert into temp table

i am trying to show usage information (products ordered) of an account. He may not order all the products, so the columns(products) are not always constant. i want to put those columns into temp table along with some other columns like date on which he has ordered and perform some operations on temp table like where conditions or groupby

Not knowing at least the number of columns in your table is something which won't work very well with something like software. Why do you have this requirement? — Tim Biegeleisen, Feb 25 '19 at 13:24
As far as I know, you can't (and I did try, I had the same requirement a while back). You can, however, use dynamic sql to first create the table, and then fill it with the dynamic query you have. What is the source for `@Query`, do you compose it yourself, or is it selected from somewhere? — HoneyBadger, Feb 25 '19 at 13:29
similar solution given in this link https://stackoverflow.com/questions/9534990/tsql-select-into-temp-table-from-dynamic-sql — Mukesh Arora, Feb 25 '19 at 14:37
And what do you then need to do with the data in the #temp table? Can that work also be performed in dynamic SQL? Otherwise you're going to have a real chicken and egg problem. — Aaron Bertrand, Feb 25 '19 at 15:11
i am trying to show usage information (products ordered) of an account. He may not order all the products, so the columns(products) are not always constant. i want to put those columns into temp table along with some other columns like date on which he has ordered and perform some operations on temp table like where conditions or groupby — venkatesh, Feb 26 '19 at 05:26
You shouldn't have products as columns, but as rows. Sounds like a design issue. — HoneyBadger, Feb 26 '19 at 07:55

score 1 · Answer 1 · answered Mar 17 '19 at 07:42

You may use #table instead of predefined @tab. This will create your table and table structure on the go.

For this first enable your ad hoc distributed queries on your server.

    sp_configure 'Show Advanced Options', 1
    GO
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    GO
    RECONFIGURE
    GO

After that you may create your #table on the basis of columns in your returned result from stored procedure.

   SELECT * INTO #table FROM 
   OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
   'EXEC dbo.procedurname')

OR

You may alter your stored procedure to fill your desired result in procedure only before returning result. For that you may use global temporary table i.e; ##table.

Note In this case make sure that you drop this ##table at the end. or check its existence before creating it.

Insert dynamic query result set into temporary table on the fly

1 Answers1