Use the result set of a stored procedure

To pipe the result set of a stored procedure just use exec command in an INSERT statement…

CREATE TABLE #Table1 (

ID int
,[Value] varchar(250)

)
GO
INSERT INTO #Table1 VALUES(1, 'one')
INSERT INTO #Table1 VALUES(2, 'two')
INSERT INTO #Table1 VALUES(3, 'three')
INSERT INTO #Table1 VALUES(4, 'four')
INSERT INTO #Table1 VALUES(5, 'five')
INSERT INTO #Table1 VALUES(6, 'six')
GO
CREATE PROCEDURE spTestInsert
AS
BEGIN
SELECT * FROM #Table1
END
GO
CREATE TABLE #Table2 (

ID int
,[Value] varchar(250)

)
GO
insert #Table2 exec spTestInsert

select * from #Table2

DROP TABLE #Table1
DROP TABLE #Table2
DROP PROCEDURE spTestInsert

Advertisements