Wednesday, June 25, 2008

Select Into Exec - Create Table From Stored Procedure Results

Most of the time you have to create a temp table and then use the Insert Into Exec statement to load the table. By using Openrowset, you can perform the equivalant of an Exec Into statement to create the table on the fly. In this case, even if you did create the temp table and tried to do the Insert Into Exec for sp_help_job, you would get an error saying that you could not have nested Insert Into Exec statements. This does have limitations though. If you try this with sp_who2, you will get an error saying that you cannot have a duplicate column name spid, becuase sp_who2 returns the spid column twice.

FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes' ,

'set fmtonly off exec msdb.dbo.sp_help_job')