What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

  • 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.

  • 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')
    

    Don't you mean `SET FMT_ONLY ON`?

    @Andreas - No because I assumed the idea was to both create and populate the table from the stored procedure output.

  • In SQL Server 2012 and above, you can use sys.dm_exec_describe_first_result_set 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. :-)

  • 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

  • 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
        );
    

    How is this different from @AaronBertrand's answer above?

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM