One common question that often arises is how to insert the results of a stored procedure into a table in SQL Server. This can be achieved in two scenarios: when the table is already created and when the table needs to be created at runtime. In this blog post, we will explore both scenarios and provide examples for each.

Scenario 1: Schema Known – Table Created Beforehand

In this scenario, if we know the schema of the stored procedure resultset, we can create the table beforehand and execute the following code:

CREATE TABLE #TestTable (
    [name] NVARCHAR(256),
    [database_ID] INT
);

INSERT INTO #TestTable
EXEC GetDBNames;

-- Select Table
SELECT * FROM #TestTable;

The disadvantage of this approach is that if the stored procedure returns more or fewer columns than expected, it will throw an error.

Scenario 2: Unknown Schema – Table Created at Runtime

In cases where we do not know the resultset of the stored procedure, we can create the table dynamically based on the resultset. We can execute the following code:

SELECT *
INTO #TestTableT
FROM OPENROWSET('SQLNCLI', 'Server=localhost; Trusted_Connection=yes;', 'EXEC tempdb.dbo.GetDBNames');

-- Select Table
SELECT * FROM #TestTableT;

The disadvantage of this approach is that it is a bit more complicated, but it works well when the column names are not known in advance. Please note that if you encounter an error while using this method, you may need to enable ad hoc distributed queries by executing the following query in SQL Server Management Studio:

sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;

We would love to hear from you! Which method do you prefer to use in your projects, and why? Let us know in the comments below.