Simplest method to insert stored procedure output into a table – SQL Server

Recently in a developer meet, I was asked a question that, “is there a simpler way to have a stored procedure output fill into a table without defining all columns, as simple as like * into #t ?” You must be thinking about OPENQUERY, yes it’s one of the solution but it has it’s own limitation. The server name has to be defined as a parameter and in a multi instance environment you have to take care of different instance names like Dev/Test/Prod etc. I would not recommend OPENQUERY inside a sp. So now we are left with only option by creating the table structure manually and then writing below query

INSERT INTO #t (col1, col2)
EXEC usp_someStoredProc

This is quite error prone. You have to be very very careful on sequence of the columns and their data types with the length. It should exactly match with the result set. This is one of the reason many developers choose table valued functions over stored procedures despite knowing the performance consequences. So being a DBA, one of our duty is to increase the productivity of developers. The root cause is defining the table structure, let’s see how can we fix that?

Note: This solution applies to SQL Server 2012 and above versions.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

%d bloggers like this: