Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

up vote 4 down vote accepted

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

CREATE PROCEDURE getDatabaseList
AS
BEGIN
    SELECT * FROM sys.databases
END

 SELECT * INTO #MyTempTable FROM 
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')

-- create temp table as usual, select , update or drop table
share|improve this answer
1  
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. –  JuanVelez 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! –  JuanVelez Mar 20 '13 at 18:18

Your Answer

 
discard

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.