How to insert result of stored procedure in global temp table?


I came across a requirement to insert result of stored procedure into a global temp table. Stored procedure name will be passed as a paramter.

1) I can’t use INSERT INTO TableName EXEC StoredProcName as diff stored procedure can return different schemas based on parameter.
2) I can’t use SELECT * INTO TableName FROM OPENROWSET(…EXEC SPName)/OPENQUERY() as ad hoc distributed queries are not enabled on the server. Also, I am not allowed enable the same.

I was wondering if there is any other way to achieve this. Thank you in advance!
more ▼

asked Jul 29 at 06:38 AM in Default

Swapnil gravatar image

31 2

How do you then handle a global temp table that has a different schema depending on which proc was run? How do you know what columns will be in it?
Jul 29 at 06:53 AM Kev Riley ♦♦
For now, I am using SELECT * INTO TableName FROM OPENROWSET(…EXEC SPName). Once the data is loaded in global temp, I use tempDb.InformationSchema to get list of columns. But, I looking for an alternate approach to avoid OPENROWSET.
Jul 29 at 08:51 AM Swapnil
Hi Grant, I would have loved to avoid this ad hoc appraoch. But, I had to go for it as there wasn't any workaround available. The scenario posted in question is one piece of work which is comibined with other pieces to accomplish the user requirement. It looks like openrowset is the only option to achieve what i am looking for. Thanks!
Jul 29 at 12:14 PM Swapnil

What version of SQL? If you are on 2012 or newer, you can use sys.dm_exec_describe_first_result_set_for_object to get the result set column list before executing the procedure (http://msdn.microsoft.com/en-us/library/ff878236.aspx).

You can use this just like you would the InformationSchema views to build a staging table for INSERT INTO ... EXEC then, after running the INSERT INTO statement, you just select the columns of interest from the staging table into the global temp table
Jul 29 at 03:30 PM KenJ
Thanks Ken! I am using SQL Server 2008 R2. But, I am glad to know that a workaround is available in 2012.
Jul 30 at 10:50 AM Swapnil
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
This is a classic sticking point. The best methods for getting stuff done within SQL Server and T-SQL require that you know, control, and define well known and well defined structures. You're saying that everything is ad hoc, but, you want to use the most efficient mechanisms. You can't have it both ways. If you have an ad hoc system, you'll have to code around the ad hoc nature of that system, which means you won't get the efficiencies that are designed into the structured aspects of the system. If you want those, you need to modify your approach so that it's structured.
more ▼

answered Jul 29 at 11:28 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(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: Jul 29 at 06:38 AM

Seen: 205 times

Last Updated: Jul 30 at 03:13 PM