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,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
(SELECT UID,LEFT(ACCT_NUM_MIN,16),LEFT( ACCT_NUM_MAX,16),BIN, BUS_ID,BUS_NM,ISO_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.

Quote:

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,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
(SELECT UID,LEFT(ACCT_NUM_MIN,16),LEFT( ACCT_NUM_MAX,16),BIN, BUS_ID,BUS_NM,ISO_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.|||

Quote:

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?|||

Quote:

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,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
(SELECT UID,LEFT(ACCT_NUM_MIN,16),LEFT( ACCT_NUM_MAX,16),BIN, BUS_ID,BUS_NM,ISO_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??|||

Quote:

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 ?|||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.|||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_MIN,16),
LEFT(ACCT_NUM_MAX,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)|||

Quote:

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_MIN,16),
LEFT(ACCT_NUM_MAX,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?|||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_MIN,16),
LEFT(ACCT_NUM_MAX,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))