create proc p_test
-- multiple resultsets
select @@servername as ServerName;
select srvname from sysservers;
create table #tmp(ServerName sysname)
I did not know that the last statement above (i.e. INSERT EXECUTE) would automatically concatenate the multiple resultsets returned from the procedure p_test and insert them into the target table, and you didn’t have to do UNION ALL explicitly. But it’s a better practice to always include UNION ALL, I think, to communicate the intention explicitly.