December 24, 2010

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:

Populate Stored Proc From Temp Table

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

id int,
names varchar(100)

EXEC proc_tests

SELECT * FROM #tests


Populate Stored Proc From Temp Table

Method 2: Use OPENROWSET function

SELECT * INTO #tests
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;
sp_configure 'Ad Hoc Distributed Queries', 1;

Then re-run the query shown in method 2.

Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

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 and a moderator at His T-sql blog is at



0 Responses to "Insert Rows in Temporary Table from Stored Procedure – SQL Server"

Copyright © 2009-2014 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions