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, 2014 at 06:38 AM in Default

avatar image

31 1 3 4

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, 2014 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, 2014 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, 2014 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, 2014 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, 2014 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, 2014 at 11:28 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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.

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, 2014 at 06:38 AM

Seen: 641 times

Last Updated: Jul 30, 2014 at 03:13 PM

Copyright 2016 Redgate Software. Privacy Policy