THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

I didn't know you could do this!

create proc p_test


  -- multiple resultsets

  select @@servername as ServerName;

  select 'abc';


  select srvname from sysservers;



create table #tmp(ServerName sysname)




EXECUTE p_test;


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.


Published Friday, November 06, 2009 12:50 PM by Linchi Shea
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Denis Gobo said:

Linchi...I discovered this when using sp_msforeachdb

here is an example

create table #tmp(DatabaseName sysname)



EXECUTE sp_msforeachdb ' select ''?'''


select * from #tmp

November 6, 2009 12:20 PM

Linchi Shea said:


That's a useful case.

November 6, 2009 1:23 PM

Daniel said:

Unfortunately you cannot use this with sp_spaceused (without parameter), because the number of columns and the types of the 2 resultsets are different.

November 6, 2009 7:31 PM

Peso said:

November 7, 2009 9:18 AM

Leave a Comment


About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement