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’)


-> 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:


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:


      EXEC StoredProcedureName

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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s