Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Microsoft Connect - Enhanced Syntax For Insert Into Statement

I've posted a feedback on Microsoft Connect about how to enhance the INSERT INTO syntax.
Especially for INSERT INTO ... EXEC ...

Sometimes when you need the result from a stored procedure, the SP itself returns two (or more) resultsets.
And it's only possibly to fetch and store the first resultset.

What I have suggested is an enhanced syntax for INSERT INTO ... EXEC, like this

INSERT INTO Table1 (Col1, Col2), Table2 (ColX, ColY, ColZ)
EXEC usp_MyStoredProcedure @Param1, @Param2

In this example, usp_MyStoredProcedure returns three resultsets, of which I want to store the two first.
First resultset has two columns, and second resultset has three columns.

Let Microsoft know what you think about this suggestion.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=470881

Print | posted on Saturday, June 27, 2009 10:06 PM | Filed Under [ Optimization SQL Server 2008 Miscellaneous ]

Feedback

Gravatar

# re: Microsoft Connect - Enhanced Syntax For Insert Into Statement

Great suggestion Peter.

However, with respect to INSERT INTO..EXEC with multiple result sets discovered something surprising the other day for a very specific case. Apparently, if both result sets have the same column datatypes, INSERT..EXEC will in fact insert them *both* into the result tables.
6/28/2009 8:58 AM | RBarryYoung
Gravatar

# re: Microsoft Connect - Enhanced Syntax For Insert Into Statement

Microsoft will have to change that then :-)
It's very easy with ADO to use .GetNextRecordset method.

I think T-SQL should support the suggested INSERT INTO syntax. It can't be that hard to change, can it?
6/28/2009 9:24 AM | Peso
Gravatar

# re: Microsoft Connect - Enhanced Syntax For Insert Into Statement

I don't know if you saw my Connect suggestion last month, Peter about a Bulk EXECUTE capability (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=440375), but the in the first response from Microsoft they apparently misunderstood it to be a request for something like what you have proposed. The response has some interesting comments about their plans in this area.
6/28/2009 5:59 PM | RBarryYoung
Gravatar

# re: Microsoft Connect - Enhanced Syntax For Insert Into Statement

However, with respect to INSERT INTO..EXEC with multiple result sets discovered something surprising the other day for a very specific case. Apparently, if both result sets have the same column datatypes, INSERT..EXEC will in fact insert them *both* into the result tables.
6/30/2009 11:20 AM | prakash
Gravatar

# re: Microsoft Connect - Enhanced Syntax For Insert Into Statement

It doesn't matter if the column datatypes are the same. If they have the same column count, SQL Server will try to insert them both in same table.
Now imagine the table has INT columns, and second resultset from stored procedure has VARCHAR columns...

The INSERT... will generate an error.
6/30/2009 12:23 PM | Peso
Gravatar

# re: Microsoft Connect - Enhanced Syntax For Insert Into Statement

The answer from Microsoft was the ordinary "We'll have the suggestion in mind and will try to incorporate it in the next version of SQL Server".
7/13/2009 9:38 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET