Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to create a temp table from a stored procedure result set without having to specify column datatypes? If not, is it possible to create a physical table at least from the SP? The reason I am asking is because I have an SP that returns too many columns for me to declare in a create table command.

Is there something like

create table from

where columns do not need to specified and sql automaticly determines column attributes??

I am running SQL server 2008

share|improve this question
up vote 4 down vote accepted

See following question from stack overflow. According to this question you can use

    SELECT * FROM sys.databases

OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')

-- create temp table as usual, select , update or drop table
share|improve this answer
You may need to add "SET FMTONLY OFF" before the stored procedure execution inside the OPENROWSET function. – spaghettidba Mar 20 '13 at 11:17
I got the following error. I guess I don't have enough privileges? SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. – PokerPlayer23 Mar 20 '13 at 12:31
Thanks for the answer @Atilla Ozgur and thanks for the tip @spaghettidba! I'm pretty sure it will work but I will have to talk to our DBA about getting the permissions. That technique will save me a lot of time once i implement it! – PokerPlayer23 Mar 20 '13 at 18:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.