Inserting the results of a stored procedure into a table

Posted on October 18, 2012

0


Recently, I needed to find out the schema of a resultset returned by a Stored Procedure so I could pass some failing SQL Unit Tests. I needed to know the columns, the column order and exact datatypes.

Without access to a client to run the procedure I needed a way to do this with just Management Studio.  I wanted to insert the results into a table that didn’t exist so I could look at the schema that got generated.

As it, eventually, turned out it is fairly easy using OPENQUERY. However, as this wasn’t immediately obvious I thought I’d blog about it in the hope this might be helpful to others.

OPENQUERY requires that you have DATA ACCESS enabled, this is because you’d normally use this to open a query from a linked server and Microsoft in all their wisdom and security conscious minds decided to have this feature disabled by default. For me, I’m going to call my local instance which OPENQUERY works just as well with.


exec sp_serveroption @server = '{Server\Instance}'
,@optname = 'DATA ACCESS'
 ,@optvalue = 'TRUE'

Now that is enabled you can perform an insert into a new table from a procedure of your choice.


SELECT * INTO new_temp FROM OPENQUERY([Server\Instance], 'EXEC db.schema.procname' )

Don’t worry, your procedure can have parameters – I’ve just left them out in this example. New_temp can be any table that doesn’t exist. When you run this it will execute the procedure and insert the results into the table.

The only real limitation is that it will take the first resultset only.

Advertisements