asked
How to store the result in Temp Table from EXEC command ?
I have following scenario- Declare @Sql varchar(1024) = 'Select * from dbo.Employee' Exec (@Sql) The above example is giving me result in Sql Server result pane. but i want to store the result in Temprory Table. How to do that ? Please reply if any one know
The best way is to create the temporary table with the correct schema, then use INSERT ... EXEC INSERT #tempTable EXEC procedureName You can also do this using OPENROWSET to allow you to avoid having to create the temp table manually - but [here is a blog post explaining why I think that's a bad idea][1]. [1]:
http://www.atlantis-interactive.co.uk/blog/post/2010/09/07/Inserting-into-a-temporary-table-from-a-stored-procedure-with-implicit-creation.aspx
Matt's right above, but if you aren't forced to use an EXEC and only want a SELECT statement's results into a temp table, you can also use the format: SELECT * INTO #myTable FROM dbo.Employee One drawback to this method is that you can't control the temp table's structure that way. The column types, widths, etc. are determined by the table(s) you are selecting them from.
To add to Mark's answer, you cannot just add **into #myTable** inside of your @Sql assignment to make it look like:
declare @Sql varchar(1024) = 'Select * into #myTable
from dbo.Employee'; This is simply because the temp table will be out of the scope once your exec finishes executing and returns control back to your session. What you can do, however, in order to preserve your original logic is something like this:
declare @Sql varchar(1024) = 'Select * into ##myTable
from dbo.Employee';
exec (@Sql);
-- you still have access to this data:
select * from ##myTable; The above will insert data into global temp table, so you will still have it available. The downside is of course that it is available to other connections as well, which might be undesirable. And you should drop it of course once you are done with it.
@Oleg, great comment. Both matt and Mark provided excellent answers, but just to be clear if you are doing a select that can be made without dynamic SQL, then Mark's technique is generally superior (see The Curse and Blessings of Dynamic SQL). If you must use dynamic SQL, then clearly Matt's is the way to go.
If it works smoothly, remember to "accept" one of the answer as correct.
You need to use a global temp table DECLARE @sql VARCHAR(4000) SET @sql = 'select * INTO ##MYTable from dbo.Employee' EXEC (@sql) SELECT * FROM ##MYTable
1 Person is following this question.
