如何将存储过程执行后的结果集放入临时表

--when you don’t know the structure that the store procedure generates:

CREATE TABLE ##1

(ID1 INT)

 

CREATE TABLE ##2

(ID1 INT

)

 

INSERT ##1

SELECT 1

 

CREATE PROC UP_TEST

AS

BEGIN

SELECT * FROM ##1

END

 

INSERT INTO ##2 EXEC UP_TEST

 

SELECT * FROM ##1

 

--otherwise:

SELECT * INTO #TEMP

FROM OPENROWSET('SQLOLEDB',

       'SERVER=MISDBS01;UID=PAS;PWD=PAS;DATABASE=MASTER',

       'EXEC SP_WHO') AS A

 

SELECT * FROM #TEMP

 

如果存储过程中用到临时表，要用SET FMTONLY OFF

 

SELECT * INTO #TEMP

FROM OPENROWSET('SQLOLEDB',

'SERVER=MISDBS01;UID=PAS;PWD=PAS;DATABASE=MASTER',

'SET FMTONLY  OFF;EXEC SP_WHO') AS A