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

Question: How to Create Temp Table From Stored Procedure?

Answer: Some questions never get old. This is one such question, which I keep on hearing for many years and I hear it often. I often get asked this question in my Comprehensive Database Performance Health Check.

There are cases when we do NOT know the resultset of the stored procedure and we want to populate the table based of it. We can execute 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 code is that it bit complicated, but it usually works well in the case of the column names are not known.

Just note that if you are getting error in this method enable ad hoc distributed queries by executing following query in SSMS.

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

Let me know if you have ever faced such situation in your business. If yes, please leave a comment as I would love to read your experience.

Just so you know not only temporary table, but you can also create a regular table using the method explained in this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)