If I read you right, you want to use the output of a given stored procedure as input to another table. There are several ways to accomplish that... one way, of course, is to make the temp table in the outer query and then use Insert/Exec to populate the table using the proc as the source of rows...

... but, you have the requirement of...

Can do. First, let's see what we can do with a built in stored procedure like "sp_who"...

--===== Create and populate the temp table using the result set of a stored procedure

-- as if it were a table.

SELECT *

INTO #Temp

FROM OPENROWSET('SQLOLEDB','Server=server\instance;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')

--===== Display the content of the new temp table to show it worked

-- and then drop the temp table for reruns.

SELECT * FROM #Temp

DROP TABLE #Temp

Notice that for this to work, you have to change "server\instance" to the instance name of your server as shown in SMS. Try it.

Now, we've proven that the method works, so why won't this work with your stored procedure (again, change the name of the server\instance AND the name of the database) ?

--===== Create and populate the temp table using the result set of a stored procedure

-- as if it were a table.

SELECT *

INTO #Temp

FROM OPENROWSET('SQLOLEDB','Server=server\instance;Trusted_Connection=Yes;Database=nameofdatabasehere',

'Set FmtOnly OFF; EXEC dbo.usp_temp_InsSample')

--===== Display the content of the new temp table to show it worked.

SELECT * FROM #Temp

DROP TABLE #Temp

Msg 7357, Level 16, State 1, Line 3

Cannot process the object "Set FmtOnly OFF; EXEC dbo.usp_temp_InsSample". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The answer is... the "rowcount" of building the table in the stored proc is treated like an empty result set! You MUST add SET NOCOUNT ON to your example proc so it looks like this...

CREATE PROC usp_temp_InsSample

AS

BEGIN

[font="Arial Black"]SET NOCOUNT ON[/font]

CREATE TABLE #MyTable

(UID INT,NAME VARCHAR(100))

INSERT INTO #MyTable SELECT 1,'Name1'

INSERT INTO #MyTable SELECT 2,'Name2'

INSERT INTO #MyTable SELECT 3,'Name3'

SELECT * FROM #MyTable

DROP TABLE #MyTable

END

... then, the code will work just fine.

There's also a way to make it so the server can be (LOCAL) for everything, but I forget how to do that... I think you have to make a "loop back" linked server called (Local), but I just don't remember. My appologies.

Last but not least... you don't need the "DataBase=" part of the code if you use a 3 part naming convention on the name of the stored procedure to be executed.

Lemme know if you have any questions on this.