How to Create Temp Table From Stored Procedure? – Interview Question of the Week #140

Question: How can I create a temporary table from a stored procedure result if I do not yet know its columns?

Answer: Some questions never get old. I hear this one often during a database performance health check. The ordinary answer, INSERT ... EXEC, assumes you have already made a table with compatible columns. The interesting version of the question is: “What if I do not know that shape in advance?”

First, see whether SQL Server can describe the procedure’s first result set. Run this in the database that contains the procedure:

SELECT column_ordinal, name, system_type_name, is_nullable, error_message
FROM sys.dm_exec_describe_first_result_set_for_object(
    OBJECT_ID(N'dbo.GetDBNames'), 0
)
ORDER BY column_ordinal;

Replace dbo.GetDBNames with your procedure. This describes metadata; it does not execute the procedure to fetch its rows. If the result shape is discoverable, define the corresponding #temp table and then use INSERT ... EXEC. That is usually the simpler, more supportable design:

-- Replace the columns below with the metadata you actually found.
CREATE TABLE #ProcedureResult
(
    DatabaseName sysname NOT NULL
);

INSERT INTO #ProcedureResult (DatabaseName)
EXEC dbo.GetDBNames;

SELECT DatabaseName FROM #ProcedureResult;

The column shown here is illustrative. Do not assume GetDBNames has that schema; verify it first. A procedure that returns different first-result shapes along different paths, uses certain dynamic SQL, or relies on temporary objects may not expose a stable shape for metadata discovery.

The original article solved a more specific requirement: create the temp table from the returned rowset, without defining its columns first. Its SELECT ... INTO #TestTableT FROM OPENROWSET(... 'EXEC ...') pattern can do that in a controlled environment. But OPENROWSET uses an OLE DB provider and a separate connection, even if that connection points to the same server. The old example specified SQLNCLI, a legacy provider, and told readers to turn on Ad Hoc Distributed Queries server-wide. Those are significant deployment and security decisions, not a routine prerequisite to paste into a production server.

The exact original create-from-rowset pattern is preserved below as a historical template. SQLNCLI is a legacy provider, not a recommendation for a new installation. The procedure and provider must exist, and the separate connection must be approved:

-- Historical provider-specific template, not executed here.
SELECT * INTO #TestTableT
FROM OPENROWSET(
    'SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
    'EXEC tempdb.dbo.GetDBNames');
SELECT * FROM #TestTableT;

If the output really must determine the table schema at runtime, have the DBA review a supported provider, authentication, encryption, provider access, and the procedure’s stable first result set before using the OPENROWSET approach. It can fail if the provider cannot determine the columns or if the second connection cannot see the same transaction state. When you control the procedure, an explicit output contract is much easier to test.

The short interview answer is: use INSERT ... EXEC after discovering and declaring a known schema. For true create-from-result behavior, SELECT ... INTO via an approved provider is possible, with environment-specific tradeoffs. Those are different requirements, and the candidate should ask which one the interviewer means.

References: Microsoft first-result-set metadata, OPENROWSET, and Ad Hoc Distributed Queries.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.