THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

as

  -- multiple resultsets

  select @@servername as ServerName;

  select 'abc';

 

  select srvname from sysservers;

go

 

create table #tmp(ServerName sysname)

go

 

INSERT #tmp

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

Comments

 

Denis Gobo said:

Linchi...I discovered this when using sp_msforeachdb

here is an example

create table #tmp(DatabaseName sysname)

go

INSERT #tmp

EXECUTE sp_msforeachdb ' select ''?'''

GO

select * from #tmp

November 6, 2009 12:20 PM
 

Linchi Shea said:

Denis;

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

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement