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

4 Answers 4

up vote 13 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
2  
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

I would write a procedure to generate the table for me:

CREATE PROCEDURE [dbo].[p_create_table_from_procedure]
    @TABLE_NAME AS NVARCHAR(MAX),
    @PROCEDURE_NAME AS NVARCHAR(MAX)

As
    DECLARE @CREATE_TABLE_QUERY NVARCHAR(MAX) = N'';


    SELECT 
        @CREATE_TABLE_QUERY += ', ' + name + ' ' + UPPER(system_type_name) + CHAR(13) + CHAR(10) + CHAR(9)

    FROM 
        sys.dm_exec_describe_first_result_set(@procedure_name, NULL, 1);


    SELECT 
        @CREATE_TABLE_QUERY = N'CREATE TABLE ' + @table_name + '(' + CHAR(13) + CHAR(10) + CHAR(9) + STUFF(@CREATE_TABLE_QUERY, 1, 1, N'') + ');';

    PRINT @CREATE_TABLE_QUERY;

Then call it with:

EXEC p_create_table_from_procedure 'YOUR_TABLE_NAME_HERE', 'YOUR_PROCEDURE_NAME_HERE'

Note: Replace 'YOUR_PROCEDURE_NAME_HERE' with the name of your own stored procedure.

Note: Replace YOUR_TABLE_NAME_HERE with the table name of your choice.

The above will generate something like this:

CREATE TABLE YOUR_TABLE_NAME_HERE(
     WeekName VARCHAR(40)
    , Line Name VARCHAR(50)
    , TheDate DATETIME
    , ReceivedAll INT
    , Answered INT
    , Abandoned INT
    , Call Length INT
    , WaitTimeAnswer INT
    , WaitTimeAbandon INT
    , PeriodName VARCHAR(10)
    , Week SMALLINT
    , Period SMALLINT
    , Year SMALLINT
    , WeekInPeriod SMALLINT
    , NumWeeksInPeriod SMALLINT
    , WeekendDate DATETIME
    , CRCOperative VARCHAR(100)
    , CallType VARCHAR(20)
    , Charge Time INT
    , SourceNumber VARCHAR(80)
    , DestinationNumber VARCHAR(80)
    , CallStart DATETIME
    , Out of Hours VARCHAR(12)
    , IsWorkingDay BIT
    );
share|improve this answer
    
How is this different from @AaronBertrand's answer above? –  Max Vernon Aug 5 at 4:03

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.