Inserting Results of a Stored Procedure/Function into a temp table

How to insert the result of a SP into a temp table without declaring the table or without knowing the output values’ data types?

So here is the solution:

We have OPENQUERY () function, generally used to query the Linked Servers.

SELECT * INTO #TempTable

FROM OPENQUERY(ServerName, ‘EXEC DataBaseName.dbo.StoredProcedureName paramvalues1, paramvalues1’)

Note:

-> ServerName: is the name of the Local server/ Linked server name. If it’s local server, we need to make sure that “Data Access” is Enabled on this server. We can do this using:

EXEC sp_serveroption @server = localservername

,@optname = ‘DATA ACCESS’

,@optvalue = ‘TRUE’

->  If it’s Linked Server then while creating the linked server, RPC should be Enabled on that using the option: Rpc = true.

-> DataBaseName is the catalog name, we want to access in the query.

 

OPENROWSET (): If we need to provide the username & password in the connection string, we can make use of OPENROWSET () function:

SELECT A.*

FROM OPENROWSET(‘SQLNCLI’, ‘Server=servername;uid=my_username;pwd=my_passoword;’, ‘EXEC DataBaseName.dbo.StoredProcedureName’) AS A;

 

SQL Server 2008:

In SQL Server 2008, we can directly use:

INSERT INTO #TempTable

      EXEC StoredProcedureName

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

3 Responses to Inserting Results of a Stored Procedure/Function into a temp table

  1. Amol says:

    gud article keep posting..!

  2. HuyDang says:

    Very useful article (added to my favourite ^_^)

  3. Thanks for the comment, HuyDang.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s