SQL Server Procedure returns multiple tables - Insert results into tables

Question

I have a procedure that returns multiple tables; eg:

PROCEDURE Something AS
BEGIN
 SELECT 1,2,3
 SELECT 4,5
 SELECT 9,10,11
END

I would like to take each table from the result and insert it into a series of tables/temp tables - one for each record set.

Is this possible?

Answer 1

You could create the temporary tables within the stored proc and push the records into that. If you are using the same session , the table would be available after the stored proc is finished.

Or you could create the temp tables before hand and call the sp to populate them.

Answer 2

if you Union the results together they would come out as one result set.

your second query only has 2 columns but this would need to be resolved either way as you put it into a table.