Selecting data from a stored procedure in SQL Server is already a documented feature and here’s an example:
insert into #systables exec sp_executesql N'select * from Northwind.sys.tables'
…problem is this example doesn’t run without first creating the temp table and therefore knowing all the column definitions. When I’m running quick queries this isn’t exactly convenient. I’ve seen blogs posts using a linked server but there’s another way :
select * into #systables from openrowset( 'sqlncli', 'server=.;trusted_connection=Yes', 'sp_executesql N''select * from Northwind.sys.tables''' )
I wouldn’t necessarily use this as a day-to-day process on a production environment but for administration or scripting installations I think it fits the bill.
I had hoped to be able to do something like this :
select top 0 * into #systables from Northwind.sys.tables
… which would have created the temporary table with the structure required given that the selection is exactly what’s required. However, it creates a little too much detail and the insert from the sproc fails. Which is a shame because in production code I’m not going to use openrowset() but I’d also prefer not to have to embed a table definition which may need to be manually kept in sync with other db definitions.
Comment by philiphendry — September 2, 2009 @ 1:15 pm