3

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

4

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
  • 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. – Juan Velez 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! – Juan Velez Mar 20 '13 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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