Insert Rows in Temporary Table from Stored Procedure – SQL Server

We can populate Temporary tables from Stored procedures using two methods.

Let us first create a Sample table and Stored Procedure as shown below:

Method 1: Create Temp Table and Populate it using Stored Procedure

CREATE TABLE #tests(
id int,
names varchar(100)
)
GO

INSERT INTO #tests
EXEC proc_tests
GO

SELECT * FROM #tests
GO
DROP TABLE #tests

OUTPUT

Method 2: Use OPENROWSET function

SELECT * INTO #tests
FROM
OPENROWSET('SQLOLEDB','Data Source=server_name;Trusted_Connection=yes;
Integrated Security=SSPI','Execute test..proc_tests')
SELECT * FROM #tests

Read more about the OPENROWSET function here

Note: By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET. So when you execute the query, you may get the message

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.

If ad hoc distributed queries are not enabled on your machine, you can enable it using the following query:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Then re-run the query shown in method 2.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan