Avoid Inserting Duplicate Entries

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • desirocks
    New Member
    • Sep 2007
    • 7
    #1

    Avoid Inserting Duplicate Entries

    Hi all,

    I am somewhat new to sql server. So help me in whatever way you can.

    I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A.

    Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

    The insert query is as below.

    table A - CORE
    table B - CRF

    INSERT INTO CRF (CORE_UID,ACCT_ NUM_MIN, ACCT_NUM_MAX,BI N, BUS_ID,BUS_NM,I SO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
    (SELECT UID,LEFT(ACCT_N UM_MIN,16),LEFT ( ACCT_NUM_MAX,16 ),BIN, BUS_ID,BUS_NM,I SO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE WHERE UID NOT IN (SELECT CORE_UID FROM CRF))



    I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

    Thank you in advance for all who try to help me out.
    Tags: None
    • ck9663
      Recognized Expert Specialist
      • Jun 2007
      • 2878
      #2
      Originally posted by desirocks
      Hi all,

      I am somewhat new to sql server. So help me in whatever way you can.

      I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A.

      Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

      The insert query is as below.

      table A - CORE
      table B - CRF

      INSERT INTO CRF (CORE_UID,ACCT_ NUM_MIN, ACCT_NUM_MAX,BI N, BUS_ID,BUS_NM,I SO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
      (SELECT UID,LEFT(ACCT_N UM_MIN,16),LEFT ( ACCT_NUM_MAX,16 ),BIN, BUS_ID,BUS_NM,I SO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE WHERE UID NOT IN (SELECT CORE_UID FROM CRF))



      I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

      Thank you in advance for all who try to help me out.

      i believe CONSTRAINT are still the best way to enforce your data integrity. you can also do this through trigger or check the table before insert, but i believe it'll be slower.

        Comment

        • desirocks
          New Member
          • Sep 2007
          • 7
          #3
          Originally posted by ck9663
          i believe CONSTRAINT are still the best way to enforce your data integrity. you can also do this through trigger or check the table before insert, but i believe it'll be slower.
          I AM NOT CONCERNED WITH THE PERFORMANCE... CAN YOU HELP ME WRITE THIS TRIGGER OR PROVIDE SYNTAX TO CHECK THE TABLE BEFORE THE INSERT?

            Comment

            • amitpatel66
              Recognized Expert Top Contributor
              • Mar 2007
              • 2358
              #4
              Originally posted by desirocks
              Hi all,

              I am somewhat new to sql server. So help me in whatever way you can.

              I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A.

              Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

              The insert query is as below.

              table A - CORE
              table B - CRF

              INSERT INTO CRF (CORE_UID,ACCT_ NUM_MIN, ACCT_NUM_MAX,BI N, BUS_ID,BUS_NM,I SO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
              (SELECT UID,LEFT(ACCT_N UM_MIN,16),LEFT ( ACCT_NUM_MAX,16 ),BIN, BUS_ID,BUS_NM,I SO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE WHERE UID NOT IN (SELECT CORE_UID FROM CRF))



              I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

              Thank you in advance for all who try to help me out.
              Your above INSERT QUERY should work fine. What is the problem that you are facing here??

                Comment

                • desirocks
                  New Member
                  • Sep 2007
                  • 7
                  #5
                  Originally posted by amitpatel66
                  Your above INSERT QUERY should work fine. What is the problem that you are facing here??

                  Since table CRF does have a composite key it doesnt allow duplicate entries and hence my application gives me an error. I want to some how check if the entry already exists or not and based upon the same i need to insert. Is there some way i can change the query ?

                    Comment

                    • rob313
                      New Member
                      • Sep 2007
                      • 16
                      #6
                      It would help to know what column(s) comprise your primary key on table B. You say that A nad B have different unique columns, but your insert statement only limits duplicates based on table A's unique key. If table B has a different unique key then that is what you need to check fo in your not in statement. Also, a NOT EXISTS should be faster, but first you need to figure out what is unique on table B.

                        Comment

                        • rob313
                          New Member
                          • Sep 2007
                          • 16
                          #7
                          OK, since you have said B has a composite key then you need something like this (I assumed that CORE_UID, BIN was the composite key, so adjust as needed):
                          INSERT INTO CRF (CORE_UID,
                          ACCT_NUM_MIN,
                          ACCT_NUM_MAX,
                          BIN,
                          BUS_ID,
                          BUS_NM,
                          ISO_CTRY_CD,
                          REGN_CD,
                          PROD_TYPE_CD,
                          CARD_TYPE)
                          SELECT UID,
                          LEFT(ACCT_NUM_M IN,16),
                          LEFT(ACCT_NUM_M AX,16),
                          BIN,
                          BUS_ID,
                          BUS_NM,
                          ISO_CTRY_CD,
                          REGN_CD,
                          PROD_TYPE_CD,
                          CARD_TYPE
                          FROM CORE A
                          WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.CORE_UID = A.UID and B.BIN = A.BIN)

                            Comment

                            • desirocks
                              New Member
                              • Sep 2007
                              • 7
                              #8
                              Originally posted by rob313
                              OK, since you have said B has a composite key then you need something like this (I assumed that CORE_UID, BIN was the composite key, so adjust as needed):
                              INSERT INTO CRF (CORE_UID,
                              ACCT_NUM_MIN,
                              ACCT_NUM_MAX,
                              BIN,
                              BUS_ID,
                              BUS_NM,
                              ISO_CTRY_CD,
                              REGN_CD,
                              PROD_TYPE_CD,
                              CARD_TYPE)
                              SELECT UID,
                              LEFT(ACCT_NUM_M IN,16),
                              LEFT(ACCT_NUM_M AX,16),
                              BIN,
                              BUS_ID,
                              BUS_NM,
                              ISO_CTRY_CD,
                              REGN_CD,
                              PROD_TYPE_CD,
                              CARD_TYPE
                              FROM CORE A
                              WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.CORE_UID = A.UID and B.BIN = A.BIN)

                              hi rob,

                              The composite key comprises of ACCT_NUM_MIN and ACCT_NUM_MAX. The UID of CORE is being transferred to CRF and CRF also has its own UID. Can you specify what whould be the change in the query now?

                                Comment

                                • rob313
                                  New Member
                                  • Sep 2007
                                  • 16
                                  #9
                                  In that case, this should work:

                                  INSERT INTO CRF (CORE_UID,
                                  ACCT_NUM_MIN,
                                  ACCT_NUM_MAX,
                                  BIN,
                                  BUS_ID,
                                  BUS_NM,
                                  ISO_CTRY_CD,
                                  REGN_CD,
                                  PROD_TYPE_CD,
                                  CARD_TYPE)
                                  SELECT UID,
                                  LEFT(ACCT_NUM_M IN,16),
                                  LEFT(ACCT_NUM_M AX,16),
                                  BIN,
                                  BUS_ID,
                                  BUS_NM,
                                  ISO_CTRY_CD,
                                  REGN_CD,
                                  PROD_TYPE_CD,
                                  CARD_TYPE
                                  FROM CORE A
                                  WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.ACCT_NUM_MIN = A.LEFT(ACCT_NUM _MIN,16) and B.ACCT_NUM_MAX = A.LEFT(ACCT_NUM _MAX,16))

                                    Comment

                                    Previous template Next
                                    Working...