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.

Many times I need to write something like the following when dealing with SQL Server.

create table #table_name
(
    column1 int,
    column2 varchar(200)
    ...
)

insert into #table_name
execute some_stored_procedure;

But create a table which has the exact syntax as the result of a stored procedure is a tedious task. For example, the result of sp_helppublication has 48 columns! I want to know whether there is any easy way to do this.

Thanks.

share|improve this question
    
If you'd like a defined output format (and if you're a DBA, you should prefer a defined format!), consider a table-value UDF. Unfortunately they have some serious limitations, so they're not always an option. –  Jon of All Trades Mar 30 '12 at 16:29

3 Answers 3

up vote 11 down vote accepted

If the procedure just returns one result set and the ad hoc distributed queries option is enabled.

SELECT * 
INTO #T 
FROM OPENROWSET('SQLNCLI', 
                'Server=(local)\MSSQL2008;Trusted_Connection=yes;',
                 'SET FMTONLY OFF;EXEC sp_who')

Or you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLNCLI', @datasrc = @@servername

SELECT *
INTO  #T
FROM OPENQUERY(LOCALSERVER, 
               'SET FMTONLY OFF;
               EXEC sp_who')
share|improve this answer
    
Don't you mean SET FMT_ONLY ON? –  Andreas Ågren Feb 13 '12 at 10:01
    
@Andreas - No because I assumed the idea was to both create and populate the table from the stored procedure output. –  Martin Smith Feb 13 '12 at 10:15

In SQL Server 2012, you will be able to do this locally, assuming the result set you are after is the first result:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += ',' + CHAR(13) + CHAR(10) + CHAR(9)
    + name + ' ' + system_type_name
    FROM sys.dm_exec_describe_first_result_set('sp_who', NULL, 1);

SELECT @sql = N'CREATE TABLE #f
(' + STUFF(@sql, 1, 1, N'') + '
);';

PRINT @sql;

Result:

CREATE TABLE #f
(
    spid smallint,
    ecid smallint,
    status nchar(30),
    loginame nvarchar(128),
    hostname nchar(128),
    blk char(5),
    dbname nvarchar(128),
    cmd nchar(16),
    request_id int
);

Note there is a limitation: if your stored procedure creates #temp tables, the metadata functionality does not work. This is why I did not use sp_who2. :-)

share|improve this answer
1  
For those who didn't notice, the 2012+ part about this solution is sys.dm_exec_describe_first_result_set. –  Nick Chammas Feb 13 '12 at 18:16
    
Yes, sorry, I could have highlighted that. –  Aaron Bertrand Feb 14 '12 at 2:28

No. The result of a stored procedure can vary wildly: it isn't designed to always return exactly one result set like a SELECT on some object.

You have to execute CREATE TABLE

share|improve this answer

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.