Executing dynamically created SQL Query and storing the Query results as a temporary table

Question

I am creating a SQL Query dynamically. After it's been created I want to execute it and store it as a temporary table.

WITH [VALIDACCOUNTS] AS( EXEC (@sqlQuery))

You don't know the columns to put in the temporary table create definition? — Thanos Markou, Jun 23 '15 at 07:26
@LukeOsborne the with keyword indicates common table expression and not a temporary table. — Thanos Markou, Jun 23 '15 at 07:29
For Dynamic Query should use `EXEC sp_executesql` staement For example `DECLARE @str NVARCHAR(100) = 'SELECT TOP 100 * FROM [dbo].[Product] ' EXEC sp_executesql @str ` but I'm not sure that you want to store in temporary table — Chanom First, Jun 23 '15 at 07:34
possible duplicate of [get resultset from stored procedure](http://stackoverflow.com/questions/6110866/get-resultset-from-stored-procedure) — Jodrell, Jun 23 '15 at 08:09
http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure — Jodrell, Jun 23 '15 at 08:10
http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure — Jodrell, Jun 23 '15 at 08:10

Ionic · Accepted Answer · 2015-06-23T08:35:50.837

1

You have two solutions for this:

As a first solution you can simply use an INSERT EXEC. This will work if you have a specified result set. This could be used if your procedure just returns one result set with a fixed result design.

Simply create your temporary table with matching columns and datatypes. After that you can call this:

INSERT INTO #yourTemporaryTable
EXEC(@sql)

The second solution would be the usage of OPENROWSET for this, which may have some sideeffects. You can read more about it here.

INSERT INTO #yourTemptable
SELECT * 
FROM OPENROWSET('SQLNCLI', 'DRIVER={SQL Server};',
                'EXEC (''+@sql+''))'

edited Jun 23 '15 at 08:35

answered Jun 23 '15 at 07:45

Ionic

3,884
1
12
33

2 or more http://www.sommarskog.se/share_data.html, also note, these INSERT-EXEC bridges cannot be nested. Also, The `INTO` is optional, its inclusion is subjective. – Jodrell Jun 23 '15 at 08:04
Yes insert Exec cannot be nested, thats true. But the second exec is optional. – Ionic Jun 23 '15 at 08:35

Executing dynamically created SQL Query and storing the Query results as a temporary table

1 Answers1