0

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))

Improve this question
12
  • And what's stopping you from achieving this? Commented Jun 23, 2015 at 7:25
  • You don't know the columns to put in the temporary table create definition? Commented Jun 23, 2015 at 7:26
  • @YannickMeeus Incorrect syntax near the keyword 'with' Commented Jun 23, 2015 at 7:28
  • @ThanosMarkou The SQL Query works Commented Jun 23, 2015 at 7:28
  • 1
    @LukeOsborne the with keyword indicates common table expression and not a temporary table. Commented Jun 23, 2015 at 7:29

1 Answer 1

Reset to default
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+''))'
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

2 or more sommarskog.se/share_data.html, also note, these INSERT-EXEC bridges cannot be nested. Also, The INTO is optional, its inclusion is subjective.
Yes insert Exec cannot be nested, thats true. But the second exec is optional.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.