SQL Server Performance

Logging ROWS not inserted due to failure in passing Primary Key and / or UNIQUE Constraints

Discussion in 'ALL SQL SERVER QUESTIONS' started by bsethi24, Mar 12, 2012.

  1. bsethi24 New Member

    Dear All,

    Hi! I am searching a solution to log all the rows those are not inserted in a table due to failure in passing Primary and/or any UNIQUE Constraint implemented on this table.

    For example,
    We have a table named "Customer" with CUSTOMERID as Primary Key and "CUSTOMERID" and "MOBILENO" as UNIQUE Constraint.

    Now, if we insert a row in this table with CUSTOMERID that already exists then SQL Server does not allow us to insert such record(s).

    OR

    If we try to insert a row with CUSTOMERID and MOBILENO that already exists again SQL SERVER does not allow us to do the same.

    Hence, I need to LOG such records with their Failure Reason (i.e. due to Primary Key failure or UNIQUE key failure) in another table?

    How can I achieve the same? Is there more than one solution for such problem & what is the best one to achieve the same??

    Regards,
    bsethi24
    bsethi24, Mar 12, 2012
    #1

  2. FrankKalis Moderator

    There is no built-in functionality that you could use. You have to create your own logic for it. Depending on your SQL Server version, you might want to have a look at MERGE, or use a separate TRY-CATCH block for this INSERT and then react on the error you receive and do the logging then.
    Is this a one-row at a time INSERT, or can there be many rows inserted at a time?
    FrankKalis, Mar 13, 2012
    #2

  3. Shehap Moderator

    As Frank said , you could use Try/Catch Commands to capture such violated records and send a notification with it to some operator , but you might check out records inserted if legal or illegal against your Unique key /PK by using Ifnotexists commands:

    Ifnotexists(select 1 from Customer where CUSTOMERID=@CUSTOMERID and MOBILENO=@MOBILENO)

    begin

    --Put your insert commands

    End

    With creating sufficient index on that table like below to avoid any performance degradation for huge data entity particularly

    createnonclusteredindex Customer_index1 on Customer(CUSTOMERID asc, MOBILENO asc)
    Shehap, Mar 21, 2012
    #3

  4. FrankKalis Moderator

    FrankKalis, Mar 21, 2012
    #4

Share This Page