Stop inserting duplicate entries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stop inserting duplicate entries

Author

Topic

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-10 : 11:10:08

Hi I am trying to insert entries in a table which has a composite primary key and i am inserting it on UID basis.

INSERT INTO TABLE_B (TABLE_B_UID,NUM_MIN, NUM_MAX,BIN, REGN_CD, PROD_CD, CARD)
(SELECT UID,LEFT(NUM_MIN,16),LEFT(NUM_MAX,16),BIN, REGN_CD, PROD_CD, CARD FROM TABLE_A WHERE UID NOT IN (SELECT TABLE_B_UID FROM TABLE B))

When i insert it tries to insert a duplicate entries and gives me an error. Since I am new to SQL SERVER 2000 i need some help. I tried IF NOT EXISTS, EXCEPT but i guess i am wrong at the syntax.

Can anybody help me out?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-10 : 11:13:46

Firstly you have to fire SELECT query in isolation and check whether you are getting duplicates there and try to remove them by adding appropriate conditions. I also suggest to use LEFT JOIN instead of NOT IN for performance reasons.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-10 : 11:18:08

I am not getting duplicates since the table_B has a composite key based on the num_min and num_max. I just want to make sure that if it already exists then do not insert otherwise insert in. Can you help me with the left join?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-10 : 11:25:54

[code]Insert into B(...)
Select ... from A LEFT JOIN B on A.UID = B.UID
Where B.UID IS NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-10 : 11:49:49

Now that i am getting the results how should i avoid inserting duplicate entries? can you help me with that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 12:09:19

1) Use a CONSTRAINT or UNIQUE INDEX.
2) See what Harsh posted above.

E 12°55'05.25"
N 56°04'39.16"

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-10 : 12:35:37

I ALREADY HAVE A UNIQUE CONSTRAINT UID
TABLE A HAS A UID WHICH IS INSERTED INTO TABLE B AND THE COLUMN IS NAMED AS TABLE_A_UID IN TABLE B.

TABLE B HAS A COMPOSITE PRIMARY KEY WHICH DOESNT ALLOW ME TO INSERT IN TABLE B. NOW THIS GIVES ME AN ERROR. I NEED SOMETHING THAT WOULD CHECK FOR AN EXISTING ENTRY IN TABLE B.

PLEASE HELP ME IN WHATEVER WAY YOU CAN GUYS.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 12:39:07

See what Harsh posted above.

E 12°55'05.25"
N 56°04'39.16"

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-11 : 10:38:25

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

Hi all,
above is the query and CRF is the table which has a composite key. It is not letting me insert duplicate entires. I want to check before inserting whether the entry already exists or not. Can anybody help me with that?

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 11:07:37

Well what's the composit key?

What do you care about the UID then, or why even have it?

Post the DDL

Anyway

WHERE NOT EXIST (SELECT * FROM TABLE_B i
WHERE o.pk1 = i.pk1 AND o.pk2 = i.pk2)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-11 : 11:26:58

Hi Brett,

the composite key is acct_num_min and acct_num_max.

Can you give me the query based upon the information or you need something else?

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 11:54:31

[code]
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 o
WHERE NOT EXISTS (SELECT * FROM CRF i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN
AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX)

[/code]

And what happens if it does exists? Do you want to do an update?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-11 : 12:28:53

No i dont want to update. What are the changes again?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 12:34:01

quote:
Originally posted by rockingdesi

What are the changes again?

What do you mean?

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 12:34:58

quote:
Originally posted by rockingdesi

No i dont want to update. What are the changes again?

Seriously?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 12:41:55

I think FUNKETEKUN is playing with us.

E 12°55'05.25"
N 56°04'39.16"

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-11 : 13:00:47

Yes i just want to check if it exists
If it exists then do not insert or update
if it doesnt then only insert...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:04:41

See what Harsh posted above.

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-11 : 13:33:13

quote:
Originally posted by Peso

See what Harsh posted above.

E 12°55'05.25"
N 56°04'39.16"

ummmm, no, you need to worry about the PK, like I posted

Is there some confusion?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 13:35:15

quote:
Insert into B(...)
Select ... from A LEFT JOIN B on A.UID = B.UID
Where B.UID IS NULL

You can always extend the JOIN as

quote:
Insert into B(...)
Select ... from A LEFT JOIN B on A.UID = B.UID AND A.Col2 = B.Col2
Where B.UID IS NULL

E 12°55'05.25"
N 56°04'39.16"

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-18 : 15:15:06

Thanks a lot Brett....

quote:
Originally posted by rockingdesi

Hi Brett,

the composite key is acct_num_min and acct_num_max.

Can you give me the query based upon the information or you need something else?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources