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)
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.
USE AdventureWorks2012 GO IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t CREATE TABLE #t (ID INT IDENTITY(1,1)) --//START: Generating table structure dynamically DECLARE @spName sysname = 'uspGetEmployeeManagers' , @columns NVARCHAR(MAX), @sql NVARCHAR(MAX) SELECT @columns = STUFF((SELECT ', ' + QUOTENAME(name) + ' ' + system_type_name + ' NULL' FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(@spName), 0) AS a ORDER BY a.column_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''); SET @sql = N'ALTER TABLE #t ADD ' + @columns EXEC sp_executesql @sql --//END: Generating table structure dynamically --//Doing insert INSERT INTO #t exec uspGetEmployeeManagers @BusinessEntityID = 3 SELECT * FROM #t