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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s