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 don’t 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 don’t 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 doesn’t execute the procedure to fetch its rows. If the result shape is discoverable, define the corresponding #temp table and then use INSERT ... EXEC. That’s 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; verify your procedure’s schema 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.

Creating the Table From the Rowset

The more specific requirement is to create the temp table from the returned rowset, without defining its columns first. SELECT ... INTO from OPENROWSET 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. It also needs Ad Hoc Distributed Queries turned on, which is a server-wide security decision, not a routine prerequisite to paste into production.

-- Provider template: needs an approved provider, Ad Hoc access and a stable first result set.
SELECT * INTO #TestTableT
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=YOUR_SERVER;Trusted_Connection=yes;Encrypt=Mandatory;',
    'EXEC YourDatabase.dbo.GetDBNames');
SELECT * FROM #TestTableT;

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

The short interview answer: 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.

An unknown result shape is not a reason to open a provider, it is a reason to ask SQL Server to describe the result first.

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