none
TSQL enhancement idea - errors virtual table that would operate similar to inserted and deleted RRS feed

  • Question

  • OUTPUT clauses and triggers can use the inserted and deleted virtual tables.  I would like to propose a new virtual table: errors.

    For instance if i am MERGing into a table from a table source and a constraint violation occurrs having an output table that contained only the errors would be fantastic

    The MERGE statement conflicted with the CHECK constraint "Valid_Address_Ycoordinate". The conflict occurred in database "?", table "dbo.tblAddress", column 'Ycoordinate'.

    In the following i have added TSQL syntax of "OUTPUT ERRORS errors.* INTO @addresses_errors " as a possible syntax for this new table.  I haven't spent the time to think through the most correct syntax but the idea has merit.  Getting 'what failed' along with the violation can be very difficult when the source data is large. This is just a handy way to get that information.  SQL already knows what failed so outputting that data as a table shouldn't be terribly difficult.  Numerous options could be created about what to do with regards to ACID compliance for the statement.  It could still fail but output the error.  It could treat the input as if it was CURSORed though and each action (inserted/update) was itself atomic.  Quite a few possibilities exist here but before i start a connect thread i thought i would throw is out for feedback.

    MERGE dbo.tblAddress trgt
    USING (
    	SELECT addr.addressid, stps.stopkeyname, stps.streetaddress, stps.city, stps.state, stps.zipcode, stps.xcoordinate, stps.ycoordinate
    	FROM dbo.tbladdress addr
    	INNER JOIN dbo.FR_Import_Stops stps ON stps.StopKeyName = addr.commonname
    ) src ON trgt.addressid = src.addressid
    WHEN MATCHED THEN
    	UPDATE SET Address=src.streetaddress,city=src.City,state=src.State,zip=src.Zipcode,Xcoordinate=src.xcoordinate,Ycoordinate=src.ycoordinate
    WHEN NOT MATCHED THEN
    	INSERT (CommonName,Address,City,State,Zip,Xcoordinate,Ycoordinate,AgencyID,CreatedByTypeID,Status,GeoCodedOperation,LoadFactor,UnloadFactor)
    	VALUES(src.stopkeyname, src.streetaddress, src.city, src.state, src.zipcode, src.xcoordinate, src.ycoordinate, src.agencyid, @Address_CreatedByTypeID,@Address_Status,@Address_GeoCodedOperation,1.0,1.0)
    OUTPUT inserted.addressid, inserted.commonname INTO @addresses_modified (addressid, StopKeyName)
    OUTPUT ERRORS errors.* INTO @addresses_errors 
    ;
    




    -- dan http://dnhlmssql.blogspot.com/

    Tuesday, December 18, 2012 3:58 PM

Answers

  • I have turned this into a connect item.  It can be found here:

      https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables


    -- dan http://dnhlmssql.blogspot.com/


    Wednesday, December 19, 2012 4:38 PM
  • I saw your post and found your idea interesting, but I couldn't really decide exactly how useful it would be. But I tossed the idea with my MVP mates, and it seems to have gained some popularity.

    In your request you say that the statement would still fail if there are errors. But I think it would be a lot more value if the statement accepted the good rows. I think the A in ACID is still upheld, as long as rows inserted whereever you divert the error rows to. More precisely, this can be a revolution for ETL scenarios or other scenarios where you know that some rows will fail, but you want the rest to pass. Today, you need to run a cursor or use SSIS.

    It would be even more cool if we could use errors in triggers. That is, I could divert rows into trigger and that would automatically undo these rows. Hey, it's Christmas, and Santa is coming, isn't he?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 20, 2012 9:24 PM

All replies

  • I like the idea of this.  Have you raised it as a suggestion on Connect?  If you have, post the link to the item and I'll support it.
    Tuesday, December 18, 2012 4:43 PM
  • I have turned this into a connect item.  It can be found here:

      https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables


    -- dan http://dnhlmssql.blogspot.com/


    Wednesday, December 19, 2012 4:38 PM
  • I saw your post and found your idea interesting, but I couldn't really decide exactly how useful it would be. But I tossed the idea with my MVP mates, and it seems to have gained some popularity.

    In your request you say that the statement would still fail if there are errors. But I think it would be a lot more value if the statement accepted the good rows. I think the A in ACID is still upheld, as long as rows inserted whereever you divert the error rows to. More precisely, this can be a revolution for ETL scenarios or other scenarios where you know that some rows will fail, but you want the rest to pass. Today, you need to run a cursor or use SSIS.

    It would be even more cool if we could use errors in triggers. That is, I could divert rows into trigger and that would automatically undo these rows. Hey, it's Christmas, and Santa is coming, isn't he?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 20, 2012 9:24 PM
  • MS responded saying they wouldn't fix this.  If you want this we, as a community, will have to make a solid case for this feature.

    Here is their response from the connect item.

      https://connect.microsoft.com/SQLServer/feedback/details/774754/new-virtual-table-errors-it-would-analogous-to-the-deleted-and-inserted-tables

    Posted by Microsoft on 4/29/2013 at 2:40 PM
    Hello,

    Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

    If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review. 

    Thanks again for reporting the product issue and continued support in improving our product.
    Gus Apostol, SQL Server Program Manager

    -- dan http://dnhlmssql.blogspot.com/

    Tuesday, May 14, 2013 5:50 PM
  • Too bad. I think your idea is excellent, at least if implemented correctly. And certinly, there is some hard thinking required to get it right. But if that were to be achieved, it would be revolutionary.

    The reply is probably a standard reply. Nevertheless, saying that this is "not common enough" is a gross misunderstanding of what is going on out there in the wild.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 14, 2013 10:15 PM
  • Voted up anyway. Good idea. 

    //Will write code for food


    Wednesday, May 15, 2013 5:46 PM
  • The item is Active again. And, gosh, the comments and the votes have really been rolling in the last few days.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 16, 2013 10:14 PM
  • Just closed again:

    http://dnhlmssql.blogspot.com/2014/10/it-is-morning-of-bad-new-for-my-connect.html


    -- dan http://dnhlmssql.blogspot.com/

    Wednesday, October 15, 2014 12:20 PM
  • Just closed again:

    http://dnhlmssql.blogspot.com/2014/10/it-is-morning-of-bad-new-for-my-connect.html

    What can I say? It's a brilliant idea, and definitely worth a better fate. I had some contacts with some MVP mates, and all were equally sad over the closing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, October 15, 2014 10:09 PM
  • And now open again with this comment:

    "We have reopened this item because it has a lot of votes. We understand what is the problem, and we will try to address it."


    -- dan http://dnhlmssql.blogspot.com/

    Tuesday, June 30, 2015 6:08 PM
  • And now open again with this comment:

    "We have reopened this item because it has a lot of votes. We understand what is the problem, and we will try to address it."

    There still is hope in this world! Thanks for the update Dan.

    576 yes votes, that's quite impressing. But it is also an excellent idea!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 30, 2015 10:35 PM