How to insert second table data into temporary table from stored proceedure

The second table will be inserted into #temp with the code you have.

Note that this will only work if both record sets have matching columns.

For example (using Microsoft's Northwind database)

CREATE PROCEDURE [dbo].[GetAllNames] 
AS 
    SELECT ContactName FROM dbo.Customers
    SELECT FirstName + ' ' + LastName FROM dbo.Employees

The first query returns 91 records and the second returns 9.

If I run the SP like this

create table #temp([name] varchar(125))

insert into #temp
exec GetAllNames

and then issue

select * from #temp

100 records will be returned.

If I change the SP

ALTER PROCEDURE [dbo].[GetAllNames] 
AS 
    SELECT ContactName FROM dbo.Customers
    SELECT FirstName,LastName FROM dbo.Employees -- note 2 columns

and attempt to run it I get an error

Quote:

Msg 213, Level 16, State 7, Procedure GetAllNames, Line 11
Column name or number of supplied values does not match table definition.

You can't access the resultsets separately in T-SQL, but you can load the results into a DataSet in C# or VB.NET and access the tables separately that way