question

Akhil avatar image
Akhil 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
stored-procedurestemporary-tableexecute
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image
Mark answered
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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
4 Likes 4 ·
@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.
1 Like 1 ·
If it works smoothly, remember to "accept" one of the answer as correct.
1 Like 1 ·
Thanks a lot for the nice suggestion .. it is working smoothly .. Thanks again.... :)
0 Likes 0 ·
Etienne avatar image
Etienne answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.