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

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

INSERT INTO #tests
EXEC proc_tests
GO

SELECT * FROM #tests
GO
DROP TABLE #tests

OUTPUT

Populate Stored Proc From Temp Table

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.


Did you like this post?
kick it on DotNetKicks.com
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 www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

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

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