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
Legacy Comments
|
RBarryYoung
2009-06-28 |
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. |
|
Peso
2009-06-28 |
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? |
|
RBarryYoung
2009-06-28 |
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. |
|
prakash
2009-06-30 |
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. |
|
Peso
2009-06-30 |
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. |
|
Peso
2009-07-13 |
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". |