INSERT the Results of a Stored Procedure with Multiple SELECTs?

[Gooser]

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables. The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables. I need to take the results of these SELECT statements and put them into tables in a different database. Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish. Can anybody help me with this? I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

 

[RiverGuy]

Well, if your dynamic SQL strings look this this:

SELECT @SomeStringVariable = 'SELECT SomeColumn, MIN(SomeOtherColumn) FROM SomeComplexDerivedTable INNER JOIN <a bunch of other tables>'

then simply change them to this:

SELECT @SomeStringVariable = 'TRUNCATE SomeTableMatchingTheSchemaOfYourResultSet; INSERT INTO SomeTableMatchingTheSchemaOfYourResultSet SELECT SomeColumn, MIN(SomeOtherColumn) FROM SomeComplexDerivedTable INNER JOIN <a bunch of other tables>'

 

[Gooser]

RiverGuy, thanks for the response. However, I can't make changes to the stored procedure because it is also used to generate reports for another application. Any further ideas?

 

[RiverGuy]

You can't even do this? This would both fill the tables and still return the resultsets.

TRUNCATE TABLE
INSERT INTO TABLE SELECT ....
SELECT * FROM TABLE

TRUNCATE TABLE
INSERT INTO TABLE SELECT ....
SELECT * FROM TABLE

TRUNCATE TABLE
INSERT INTO TABLE SELECT ....
SELECT * FROM TABLE

--etc.

If not, then I don't know what you to tell you. There's not always a duct-tape solution to every problem. Sometimes you have to bite the bullet and do it the right way.

 

[gmmastros]

Do you mean to say that this one stored procedure (that you cannot change) returns 5 result sets, and you need to handle each one separately? Am I understanding correctly?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

George- There are 5 different SELECT statements in the stored procedure, I need to port the results of each into separate tables, i.e. RiskAddress, RiskDetails, RiskOwnerDetails, RiskConstructionDetails, RiskProtectionDetails. (For us, a "Risk" is a basic unit of work, it is an insurable property upon which our field people do inspections.)

 

[markros]

You may want to see the answer on this same question in MSDN. Unfortunately, the answer is still no - it can not be done in T-SQL.

PluralSight Learning Library

 

[Gooser]

Can not be done?

 

[markros]

NO.

PluralSight Learning Library

 

[djj55]

This can be done in .NET

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[simian336]

If I understand what you are saying.... You could go into the stored procedure have it insert the SQL statements into a table when it runs. Then you could use those staments to populate your tables.

it probablay has some code like
Set @sql= 'Select * from...."

Exec (@sql)

then you could insert the next like like...

insert tempdable (sqlstatments) values (@sql)

Just a thought

Simi

 

[jshurst]

Can you use .NET for this? If I'm understanding correctly then you have 1 SP that returns 5 result sets and you basically want to split them up. An ADO.NET DataSet will do this automatically for you (it will let you work with each result set). I don't know how to do this in SQL though.

...I'll assume that you already can execute the SP and return the results to .NET

DataSet ds;
SqlDataAdapter da = new SqlDataAdapter();
da.Fill(ds);

//So for resultset 1
ds.Tables(0).Rows.Count;

//Resultset 2
ds.Tables(1).Rows.Count

//etc

So now you have different resultsets (or tables) for each of your 5 select statments (coming from your 1 stored proc).

I might be way-off base here, but this is what I'm understanding your problem to be. Also, I don't know how to do this in SQL without using .NET. Also, I wrote this code directly on this page so it's certainly not complete.

Regards,

J

 

[markros]

Exactly. It's easy to get multiple results in .NET, but seems like there is no way to get them in T-SQL. May be it's time to ask for this feature added to SQL Server. If so, you can post a Connect item. If you do, please post link here, we will vote it up.

PluralSight Learning Library