Insert Results of a Stored Procedure into a Temporary Table

Insert stored procedure results into temp table

Hard to say without seeing the code to the stored procedure, but my guess is that the procedure also creates a temp table named #tmp. Try creating a temp table with a different name and running your INSERT EXEC into that, or post the code to the procedure so we can see it.

Trying to insert a stored procedure into a temporary table, getting 'an object or column name is missing or empty'

Try creating the temp table first:

CREATE TABLE #temp1
(
   COL1 INT,
   COL2 VARCHAR(MAX)   
)

INSERT INTO #temp1 
exec alexander.dbo.get_uberrecords '20120101', '20120201', 'labcorp'

In your case of an extremely wide result set, you may want to use OPENROWSET

In any case, this SO has many options:
Insert results of a stored procedure into a temporary table

I want to Execute stored procedure into temporary table but it gives me an error You must specify a table where to make the selection

You can't SELECT from a Stored Procedure. Something like SELECT * FROM EXECUTE dbo.MySP isn't going to work.

You can still INSERT the data from a Stored Procedure into a table, however, you must first define the table, and then INSERT the data. This is Pseudo-SQL as we have no definitions of your objects, however, this should get you on the right path:

CREATE TABLE #MyTempTable({Column1} {Data Type}[,
                          {Other Column(s)} {Column DataType(s)} ..... ]);

INSERT INTO #MyTempTable ({Columns List})
EXECUTE dbo.MyStoredProcedure @MyParam;

---

Related Topics