Outputting Sproc result set to a new physical table

Question

Post reply

Outputting Sproc result set to a new physical table

JaybeeSQL

Hall of Fame

Points: 3496
More actions
August 3, 2015 at 12:35 pm

#319430

Hi all,

I figure I need to output a sproc into a new physical table, so the column definitions match the output.

So far I think I'll need a

Select Into DbName.NewTableName

Followed by an

Insert Into DbName.NewTableName

From (SprocNameHere),

Any ideas people?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

You can't create the table with the execution of a proc. You'll need to first CREATE TABLE, and then INSERT INTO ... EXEC...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JaybeeSQL Hall of Fame Points: 3496 More actions · Answer 2

JaybeeSQL

Hall of Fame

Points: 3496

August 3, 2015 at 1:04 pm

#1816941

GilaMonster (8/3/2015)

You can't create the table with the execution of a proc. You'll need to first CREATE TABLE, and then INSERT INTO ... EXEC...

Will I need to supply column definitions to the Create Table statement? If so, I guess I'll need to get those from the Sproc - which in this case is a lot of work...

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Well, yes. It'll be a stock, standard CREATE TABLE statement. As I said, you can't create the table from the execution of the procedure. You'll need to create the table first, then you can run the INSERT ... EXEC statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JaybeeSQL Hall of Fame Points: 3496 More actions · Answer 4

JaybeeSQL

Hall of Fame

Points: 3496

August 3, 2015 at 1:30 pm

#1816949

GilaMonster (8/3/2015)

Well, yes. It'll be a stock, standard CREATE TABLE statement. As I said, you can't create the table from the execution of the procedure. You'll need to create the table first, then you can run the INSERT ... EXEC statement.

Thanks for taking the time Gail 🙂

ScottPletcher SSC Guru Points: 100262 More actions · Answer 5

I've actually resorted to having some procs accept a special param value that "tells" the proc just to return the current create table command for its result table. Then the outer code can run that code to create the table then run the proc again to get the result. Yes, it's a pain to do the proc code, but if the result table structure changes for that proc, it prevents run-time errors.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

JaybeeSQL (8/3/2015)

Hi all,

I figure I need to output a sproc into a new physical table, so the column definitions match the output.

So far I think I'll need a

Select Into DbName.NewTableName

Followed by an

Insert Into DbName.NewTableName

From (SprocNameHere),

Any ideas people?

You can call the proc using things like OPENROWSET to do this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 7

Eirikur Eiriksson

SSC Guru

Points: 182631

August 3, 2015 at 5:14 pm

#1816976

Quick suggestion, use sys.dm_exec_describe_first_result_set or sys.dm_exec_describe_first_result_set_for_object, to get the structure of the output of the stored procedure.

😎

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Not as convenient as a SELECT INTO, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sgmunson SSC Guru Points: 110588 More actions · Answer 9

Eirikur Eiriksson (8/3/2015)

Quick suggestion, use sys.dm_exec_describe_first_result_set or sys.dm_exec_describe_first_result_set_for_object, to get the structure of the output of the stored procedure.

😎

According to MS doc, that applies only to SQL 2014 and above, and this post is in a 2008R2 forum... unless it's "undocumented" in the earlier version ?

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 10

sgmunson (8/4/2015)

Eirikur Eiriksson (8/3/2015)

Quick suggestion, use sys.dm_exec_describe_first_result_set or sys.dm_exec_describe_first_result_set_for_object, to get the structure of the output of the stored procedure.

😎

According to MS doc, that applies only to SQL 2014 and above, and this post is in a 2008R2 forum... unless it's "undocumented" in the earlier version ?

My bad, the procedure sp_describe_first_result_set was introduced on 2012, along with the the dms. On 2005 and later one can use FMTONLY

😎

sgmunson SSC Guru Points: 110588 More actions · Answer 11

Eirikur Eiriksson (8/4/2015)

sgmunson (8/4/2015)

Eirikur Eiriksson (8/3/2015)

Quick suggestion, use sys.dm_exec_describe_first_result_set or sys.dm_exec_describe_first_result_set_for_object, to get the structure of the output of the stored procedure.

😎

According to MS doc, that applies only to SQL 2014 and above, and this post is in a 2008R2 forum... unless it's "undocumented" in the earlier version ?

My bad, the procedure sp_describe_first_result_set was introduced on 2012, along with the the dms. On 2005 and later one can use FMTONLY

😎

Okay, but FMTONLY requires a tool other than SSMS if you want to derive a CREATE TABLE statement from what you get back, which is just an empty recordset. With the dmv, you get a table back that can be used to derive a CREATE TABLE statement, fairly easily.

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 12

sgmunson (8/4/2015)

Eirikur Eiriksson (8/4/2015)

sgmunson (8/4/2015)

Eirikur Eiriksson (8/3/2015)

Quick suggestion, use sys.dm_exec_describe_first_result_set or sys.dm_exec_describe_first_result_set_for_object, to get the structure of the output of the stored procedure.

😎

According to MS doc, that applies only to SQL 2014 and above, and this post is in a 2008R2 forum... unless it's "undocumented" in the earlier version ?

My bad, the procedure sp_describe_first_result_set was introduced on 2012, along with the the dms. On 2005 and later one can use FMTONLY

😎

Okay, but FMTONLY requires a tool other than SSMS if you want to derive a CREATE TABLE statement from what you get back, which is just an empty recordset. With the dmv, you get a table back that can be used to derive a CREATE TABLE statement, fairly easily.

Not really, just grab the execution plan and read it from there

😎

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Eirikur Eiriksson (8/4/2015)

sgmunson (8/4/2015)

Eirikur Eiriksson (8/4/2015)

sgmunson (8/4/2015)

Eirikur Eiriksson (8/3/2015)

Quick suggestion, use sys.dm_exec_describe_first_result_set or sys.dm_exec_describe_first_result_set_for_object, to get the structure of the output of the stored procedure.

😎

According to MS doc, that applies only to SQL 2014 and above, and this post is in a 2008R2 forum... unless it's "undocumented" in the earlier version ?

My bad, the procedure sp_describe_first_result_set was introduced on 2012, along with the the dms. On 2005 and later one can use FMTONLY

😎

Okay, but FMTONLY requires a tool other than SSMS if you want to derive a CREATE TABLE statement from what you get back, which is just an empty recordset. With the dmv, you get a table back that can be used to derive a CREATE TABLE statement, fairly easily.

Not really, just grab the execution plan and read it from there

😎

Not exactly an automatic solution, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

Ok... here's a very simple demonstration of how it can be done with OPENROWSET. The following will execute sp_DirTree for C:\Temp and creates a temporary table from it using SELECT INTO.

Before you try to run this code change "serverrnamehere" and "instancenamehere" to the appropriate server\instance name.



--===== Conditionally drop temp tables to make reruns easier in SSMS.

     IF OBJECT_ID('tempdb..#MyFiles','U') IS NOT NULL DROP TABLE #MyFiles;

--Create a table from the output of a stored procedure.

 SELECT IDENTITY(INT,1,1) AS RowNum, *

   INTO #MyFiles

   FROM OPENROWSET('SQLOLEDB','Server=serverrnamehere\instancenamehere;Trusted_Connection=Yes;Database=Master',

                              'Set FmtOnly OFF; EXEC dbo.xp_DirTree ''C:\Temp'',1,1')

  WHERE [File] = 1

  ORDER BY SubDirectory

;

 SELECT * FROM #MyFiles

;

Now, there are a couple of "problems" with this. First, whatever runs this needs sysadmin privs. That's normally not a problem for stored procedures using WITH EXECUTE AS OWNER. Of course, never give non-DBA users sysadmin privs. They should only do things by stored procedure.

You also need to allow ad hoc queries.

It also can't take variables. If you want to force it to take variables, you'll have to do it using dynamic SQL along with the normal advisory of sanitizing inputs to prevent SQL Injection.

If you can't do any of the sysadmin thing or enable ad hoc queries, the you might try OPENQUERY or OPENDATASOURCE in a similar fashion.

I'll also tell you that it actually runs the stored procedure twice, if I recall correctly. One to format the output and one to produce the output. Remember that if you need to guarantee that the stored procedure will only run once.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions