Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
TSQL enhancement idea - errors virtual table that would operate similar to inserted and deleted

Answered TSQL enhancement idea - errors virtual table that would operate similar to inserted and deleted

  • Tuesday, December 18, 2012 3:58 PM
     
      Has Code
    Sign In to Vote
    0

    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/

     

All Replies

  • Tuesday, December 18, 2012 4:43 PM
     
     
    Sign In to Vote
    0
    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.
     
  • Wednesday, December 19, 2012 4:38 PM
     
     Answered
    Sign In to Vote
    1

    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/


     
  • Thursday, December 20, 2012 9:24 PM
     
     Answered
    Sign In to Vote
    1

    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

     
  • Tuesday, May 14, 2013 5:50 PM
     
     
    Sign In to Vote
    0

    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 10:15 PM
     
     
    Sign In to Vote
    0

    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
     
  • Wednesday, May 15, 2013 5:46 PM
     
     
    Sign In to Vote
    0
    Voted up anyway. Good idea. 

    //Will write code for food


     
  • Thursday, May 16, 2013 10:14 PM
     
     
    Sign In to Vote
    0
    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