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
more ▼

asked Sep 29 '10 at 02:35 AM in Default

Akhil gravatar image

32 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Sep 29 '10 at 02:38 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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:

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.
more ▼

answered Sep 29 '10 at 07:31 AM

Mark gravatar image

2.6k 21 25 27

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.
Sep 29 '10 at 08:06 AM Oleg
@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.
Sep 29 '10 at 12:58 PM TimothyAWiseman
Thanks a lot for the nice suggestion .. it is working smoothly .. Thanks again.... :)
Oct 04 '10 at 10:46 PM Akhil
If it works smoothly, remember to "accept" one of the answer as correct.
Oct 04 '10 at 11:05 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

You need to use a global temp table

SET @sql = 'select * INTO ##MYTable from dbo.Employee'

EXEC (@sql)

more ▼

answered Jul 31 '13 at 09:22 AM

Etienne gravatar image

70 3 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 29 '10 at 02:35 AM

Seen: 31925 times

Last Updated: Jul 31 '13 at 09:22 AM