Inserting the results of a stored procedure into a temp table.

I recently had a co-worker who was unable to duplicate the results of a well known, and assumed working, stored procedure, and wanted to find out if I could help them with getting data from a stored procedure.  The problem?  The stored procedure had nearly 100 columns, and many of the discussions on the internet basically made it very difficult to figure out the best way to get the information.  There are a LOT of methods out there.  One uses OpenRowset, but this has configuration issues in many places. Others use fantastic methods revolving around the use of XML parsing, or even dropping it into C#.  These are OK, for their needs, but in reality if you have access to the stored procedure, this is one other method that you can use that is MUCH quicker as long as you are using sql server management studio.

The method I use is to do this.

1.  Copy the code for the stored procedure, do this is in a test environment where you have full access.
2.  Change the name of the stored proc (creating a new one) and add a unique word to the end, I call my procs that I do this with <procname>_CREATETEMPTABLE.
3.  Open the procedure, find the result set that you want to get the results from, and instead of return the select statement to the proc.  Add a an insert into statement into a dbo.<insertclevernamehere>
4.  Open SQL Server Management Studio and find the table dbo.<insertclevernamehere> and right click, and get the schema by going to Script Table As->Create->New Query Window.
5.  Copy the guts into a create table script for a temp table.
6.  Run the stored procdure by doing

Insert into #mytemptable
(
...columns
)
exec <yourstoredprocedure>

And it should work every time.

So, how does this work in real life?

Lets say I have a stored procedure, call it.

dbo.MyExampleProc

And lets say it has 2 columns returned, but we will do this as if it had 100.

The proc is defined as

create proc dbo.myexampleproc

as

SELECT

cast(1 as varchar(10)) as col1,cast(2 as int) as col2

When we run the proc, it does not return the schema, and in our case we can't figure out what that Schema SHOULD be without a lot of manual work or looking at documentation that no longer exists.

The next step would be to make it so that the data goes into a temporary, but accessable database table.

So we change the proc and do.

create proc dbo.myexampleproc_CREATETEMPTABLE

as

SELECT

cast(1 as varchar(10)) as col1,cast(2 as int) as col2

into dbo.myexampleproc_CREATETEMPTABLE_OUTPUT

Now that the data is in the table dbo.myexampleproc_CREATETEMPTABLE_OUTPUT we then script the table out using Script Table As->create->New Query Window.

This provides the data definiton:

CREATE TABLE [dbo].[myexampleproc_CREATETEMPTABLE_OUTPUT](

       [col1] [varchar](10) NULL,

       [col2] [int] NULL

) ON [PRIMARY]

Then I delete the table myexampleproc_CREATETEMPTABLE_OUTPUT and the stored procedure myexampleproc_CREATETEMPTABLE.

So then we can stick this somewhere and have a useful script with it to look at data, for example, the below, and we are done.  

USE ETLExamples

/* generate schema step, create a new stored proc exactly like the old one, execute it but make the result set go into a dbo.<temptablename>

For example, I created a temp one with _CREATETEMPTABLE, and in the final result set i had it go to a table dbo.<temptablename>

I ran it with the parameters (example below) that are the same paramters I want to capture.

I then deleted the stored proc, then used sql server management studio to grab the schema of the table (right click on the table, script)

Then I took the table column definitions and inserted them into the create table script for the temp table below.

Then when you execute the

INSERT INTO <temptable>

EXEC <stored proc name>

It just inserts the results of the proc into the temp table.

*/

--exec dbo.myexampleproc

IF object_id('tempdb..#myreallyfancytemptable') IS NOT NULL

BEGIN

   DROP TABLE #myreallyfancytemptable

END

--- STep 1, create the definition of the table that exactly matches the definition of your stored procedure

create table #myreallyfancytemptable

(

       [col1] [varchar](10) NULL,

       [col2] [int] NULL

)

-- insert into the temp table.

INSERT INTO #myreallyfancytemptable

EXEC [dbo].[myexampleproc]

select

* from #myreallyfancytemptable

The results from the table are:

col1        col2

1              2