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

Question: How to Create Temp Table From Stored Procedure?

How to Create Temp Table From Stored Procedure? - Interview Question of the Week #140 temptablesp-800x233

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)

SQL Scripts, SQL Server, SQL Stored Procedure, Temp Table
Previous Post
How to Insert Line Break in SQL Server String? – Interview Question of the Week #139
Next Post
How to Kill User Sessions (SPID) in SQL Server? – Interview Question of the Week #141

Related Posts

1 Comment. Leave new

  • KOMPELLA LAXMI NARASIMHA MURTHY
    September 17, 2017 10:16 am

    Good one.

    Yes, we had the issue and ofcourse managed to get the required but it may not be the best.

    The scenario is like we get different set of columns every time based on the parameter we are passing to the stored procedure.

    Thanks.

    Reply

Leave a Reply