Microsoft Connect - Enhanced Syntax For Insert Into Statement

<< Microsoft Connect - SSMS Debugger Issue | Home | Microsoft MVP >>

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

Feedback

# 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

# 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

# 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

# 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

# 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

# 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.

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories