🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

Insert results of a stored procedure into a temporary table

How do I do a SELECT INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select all data from BusinessLine into tmpBusLine works fine.


select *

into tmpBusLine

from BusinessLine

I am trying the same, but using a stored procedure that returns data, is not quite the same.


select *

into tmpBusLine

from

exec getBusinessLineHistory '16 Mar 2009'

*Output message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.
sql-server sql
by

2 Answers

Sonali7
If you want to abandon the use of a stored procedure for a user-defined function, then you can use an inline table-valued user-defined function. This is essentially a stored procedure (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement.

For example, there is an inline table-valued user-defined function to get a list of customers for a particular region:

CREATE FUNCTION CustomersByRegion 

(  

    @RegionID int  

)

RETURNS TABLE 

AS

RETURN 

  SELECT 

  FROM customers

  WHERE RegionID = @RegionID

GO


Then, you can call this function to get what your results a such:
SELECT * FROM CustomersbyRegion(1)


Or to do a SELECT INTO:
SELECT * INTO CustList FROM CustomersbyRegion(1)


If still there is a need for a stored procedure, then wrap the function as below:
CREATE PROCEDURE uspCustomersByRegion 

(  

    @regionID int  

)

AS

BEGIN

     SELECT  FROM CustomersbyRegion(@regionID);

END

GO
espadacoder11
Easiest Solution:
CREATE TABLE #temp (...);


INSERT INTO #temp

EXEC [sproc];

If you don't know the schema then you can do the following. Please note that there are severe security risks in this method.


SELECT * 

INTO #temp

FROM OPENROWSET('SQLNCLI', 

                'Server=localhost;Trusted_Connection=yes;', 

                'EXEC [db].[schema].[sproc]')

Login / Signup to Answer the Question.

LoginSignup