How to store the result in Temp Table from EXEC command ?

Question

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

Answer 1

0

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.

answered Sep 29 '10 at 02:38 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

Answer 2

0

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.

answered Sep 29 '10 at 07:31 AM

Mark
2.6k ● 20 ● 23 ● 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)

How to store the result in Temp Table from EXEC command ?

Follow this question

Related Questions