By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,070 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,070 IT Pros & Developers. It's quick & easy.

Avoid Inserting Duplicate Entries

P: 7
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.
Sep 11 '07 #1
Share this Question
Share on Google+
8 Replies


ck9663
Expert 2.5K+
P: 2,878
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.
Sep 11 '07 #2

P: 7
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?
Sep 11 '07 #3

amitpatel66
Expert 100+
P: 2,367
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??
Sep 11 '07 #4

P: 7
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 ?
Sep 11 '07 #5

P: 16
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.
Sep 11 '07 #6

P: 16
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)
Sep 11 '07 #7

P: 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_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?
Sep 11 '07 #8

P: 16
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))
Sep 20 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.