SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stop inserting duplicate entries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/10/2007 :  11:10:08  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/10/2007 :  11:13:46  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/10/2007 :  11:18:08  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
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?
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/10/2007 :  11:25:54  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Insert into B(...)
Select  ... from A LEFT JOIN B on A.UID = B.UID
Where B.UID IS NULL


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/10/2007 :  11:49:49  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Now that i am getting the results how should i avoid inserting duplicate entries? can you help me with that?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/10/2007 :  12:09:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
1) Use a CONSTRAINT or UNIQUE INDEX.
2) See what Harsh posted above.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/10/2007 :  12:35:37  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/10/2007 :  12:39:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See what Harsh posted above.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/11/2007 :  10:38:25  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
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?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2007 :  11:07:37  Show Profile  Reply with Quote
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



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/11/2007 :  11:26:58  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
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?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2007 :  11:54:31  Show Profile  Reply with Quote

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)



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




Edited by - X002548 on 09/11/2007 11:55:23
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/11/2007 :  12:28:53  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
No i dont want to update. What are the changes again?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/11/2007 :  12:34:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by rockingdesi

What are the changes again?
What do you mean?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2007 :  12:34:58  Show Profile  Reply with Quote
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



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/11/2007 :  12:41:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think FUNKETEKUN is playing with us.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/11/2007 :  13:00:47  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Yes i just want to check if it exists
If it exists then do not insert or update
if it doesnt then only insert...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/11/2007 :  13:04:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See what Harsh posted above.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/11/2007 :  13:33:13  Show Profile  Reply with Quote
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



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/11/2007 :  13:35:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  15:15:06  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
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?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000