| Author |
 Topic  |
|
|
rockingdesi
Starting Member
USA
31 Posts |
 Posted - 09/10/2007 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 09/10/2007 : 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
USA
31 Posts |
Posted - 09/10/2007 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 09/10/2007 : 11:25:54
|
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" |
|
|
|
rockingdesi
Starting Member
USA
31 Posts |
Posted - 09/10/2007 : 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
Sweden
29138 Posts |
Posted - 09/10/2007 : 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
USA
31 Posts |
Posted - 09/10/2007 : 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
Sweden
29138 Posts |
Posted - 09/10/2007 : 12:39:07
|
See what Harsh posted above.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
rockingdesi
Starting Member
USA
31 Posts |
Posted - 09/11/2007 : 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 |
|
|
rockingdesi
Starting Member
USA
31 Posts |
Posted - 09/11/2007 : 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 - 09/11/2007 : 11:54:31
|
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 |
|
|
|
rockingdesi
Starting Member
USA
31 Posts |
Posted - 09/11/2007 : 12:28:53
|
| No i dont want to update. What are the changes again? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/11/2007 : 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/11/2007 : 12:41:55
|
I think FUNKETEKUN is playing with us.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
rockingdesi
Starting Member
USA
31 Posts |
Posted - 09/11/2007 : 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
Sweden
29138 Posts |
Posted - 09/11/2007 : 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/11/2007 : 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 asquote:
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
USA
31 Posts |
Posted - 09/18/2007 : 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?
|
|
|
| |
Topic |
|
Stop inserting duplicate entries
