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

1 Answer 1


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
  • 1
    You may need to add "SET FMTONLY OFF" before the stored procedure execution inside the OPENROWSET function. Mar 20, 2013 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.
    – Juan Velez
    Mar 20, 2013 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!
    – Juan Velez
    Mar 20, 2013 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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