I have a stored procedure that returns a result set to me. I want to insert those results into a table. Can I do this?
You can only insert the result set from a stored procedure into an existing table whose schema matches the output from the procedure. The syntax is as follows:
INSERT INTO SomeTable EXEC SomeProcedure
There is also a solution using OPENROWSET which Rob Farley mentioned in an answer below.
You can also use OPENROWSET to turn the results of a stored procedure into a table-expression, and then use SELECT...INTO to create a new table with the results. Bear in mind you can't do this with
sp_who2 because it has two columns with the same name (and if you look in the source for it, you'll see it's for 'right-scrolling users'.
I'll put an example together when I'm not on my phone.
Edited: Here's that example...
SELECT * INTO #SomeNewTable FROM OPENROWSET('SQLNCLI','Server=.;Trusted_Connection=yes;','EXEC sp_who')