---
title: "How to create Temp Table for Stored Procedure in SQL Server?"  
description: "How to create Temp Table for Stored Procedure in SQL Server?"  
author: "Steilla Mitchel"  
published: 2021-10-21  
updated: 2021-10-21  
canonical: https://www.mindstick.com/forum/156791/how-to-create-temp-table-for-stored-procedure-in-sql-server  
category: "mssql server"  
tags: ["sql server", "sql", "sql server 2008", "sql server 2012", "sql-server-2016"]  
reading_time: 2 minutes  

---

# How to create Temp Table for Stored Procedure in SQL Server?

How to create a [temporary table](https://www.mindstick.com/articles/12546/temporary-table-in-sql) for [Stored Procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net)?

## Replies

### Reply by Steilla Mitchel

**SQL Create Temp [table](https://www.mindstick.com/articles/43918/how-to-design-table-using-bootstrap) for [stored](https://www.mindstick.com/forum/157561/what-is-the-stored-procedure-create-a-procedure-to-find-the-record-by-stu_id-from-the-student-table) [Procedure](https://www.mindstick.com/forum/32/stored-procedure-return-datatype):**

Sometimes we forget about result set of created Stored Procedure and we have need to create a [temporary](https://www.mindstick.com/forum/2292/how-to-set-temporary-path-of-jdk-in-windows) table based on that stored procedure then try the following SQL statement to create a temp table base in the stores procedure by using the following SQL statement-

Syntax-

```
SELECT * INTO TempTableName FROM OPENROWSET(‘SQLNCLI’, ‘SERVER=Localhost; Trusted_connection= yes;’, ‘EXEC ProcedureName’);
```

Then

```
SELECT * from TempTable;
```

Ex-

First I have create a Stored procedure

```
CREATE PROCEDURE usp_DemoProcedure
AS
BEGIN
 SELECT * FROM Employees
END
```

Here the following SQL statement is used to create a temp table to store the result set of the stored procedure,

```
SELECT * INTO #tblTempTable FROM
OPENROWSET('SQLNCLI', 'Server= localhost; Trusted_Connection=yes;', 'EXEC usp_DempProcedure');
```

Execute the temp table

```
SELECT * FROM #tblTempTable;
```

If you are getting error to perform the above SQL statement then you need to enable the ad hoc distributed queries by using the following queries,

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

If you want to show all advance system setting then use the following SQL statement,

```
sp_configure
```

To set the default system setting use the following statement,

```
sp_configure 'Show Advanced Options', 0
```

## \


---

Original Source: https://www.mindstick.com/forum/156791/how-to-create-temp-table-for-stored-procedure-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.