# Miscellaneous > General Database Discussions >  BCP and Duplicate primary keys

## David Nguyen

Hi All,
    I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`. 
    Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`.
    I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file.
     TIA and you have a great day.
David Nguyen.

----------


## David Nguyen

Hi All,
     I just figure out a way to locate record(s) with duplicate key(s) in
the imported data file. Let`s say we have table A with two columns id,date and
the composite key  `id+date` is unique. Now, I bcp a file `IMPORT.DAT` into that
table A, BCP failed because of `violation of primary key constraint` and BCP does
not tell me exactly which record has duplicate key. 
Step 1. create a new table duplicate of table A by "select # into A__DUP
from A. New table ADUP has identical structure like A EXCEPT it has NO
UNIQUE INDEXED KEY like table A does AND table ADUP has also all the data
from old table A.
Step 2. bcp server..ADUP in IMPORT.DAT -c -t	 -r
 -Usa -P 
This time BCP won`t fail because there is no unique primary key constraint at
all.
Step 3. Locate record(s) with duplicate key(s) by :
`select id,date,count(#) from ADUP group by id,date having count(#) > 1 
   Now with those duplicate ID,date you can edit and/or recreate those
import data file without those records which caused BCP to fail.

David Nguyen




On 9/10/98 10:17:00 AM, David Nguyen wrote: 
> Hi All,
    I`m using BCP to import ASCII data text into a table that 
> already has many records. BCP failed because of `Duplicate primary key`. 

>    Now, is there any way using BCP to know precisely which record whose 
> primary key caused that `violation of inserting duplicate key`.
    I 
> already used the option -O to output error to a `error.log`, but it doesn`t 
> help much, because that error log contains the same error message mentioned 
> above without telling me exactly which record so that I can pull that 
> `duplicate record` out of my import data file.
     TIA and you have a 
> great day.
David Nguyen.

----------


## Jonathan Yang

want to BCP file F.txt=> table t?

1. make a work table w by  "select * into w from t"
2. "truncate table w"
3. BCP f.txt ==> W
4. Select from W,T by join on keys.

That`s it

GD luck





On 9/10/98 10:17:00 AM, David Nguyen wrote: 
> Hi All,
    I`m using BCP to import ASCII data text into a table that 
> already has many records. BCP failed because of `Duplicate primary key`. 

>    Now, is there any way using BCP to know precisely which record whose 
> primary key caused that `violation of inserting duplicate key`.
    I 
> already used the option -O to output error to a `error.log`, but it doesn`t 
> help much, because that error log contains the same error message mentioned 
> above without telling me exactly which record so that I can pull that 
> `duplicate record` out of my import data file.
     TIA and you have a 
> great day.
David Nguyen.

----------


## David Nguyen

Hi Jonathan Yang,
    Can you clarify more in your second step"truncate table w"
    Thank You.
David Nguyen

On 9/10/98 1:47:38 PM, Jonathan Yang wrote: 
> want to BCP file F.txt=> table t?

1. make a work table w by  "select * 
> into w from t"
2. "truncate table w"
3. BCP f.txt ==> W
4. Select from 
> W,T by join on keys.

That`s it

GD luck





On 9/10/98 10:17:00 
> AM, David Nguyen wrote: 
> Hi All,
    I`m using BCP to import ASCII data 
> text into a table that 
> already has many records. BCP failed because of 
> `Duplicate primary key`. 

>    Now, is there any way using BCP to know 
> precisely which record whose 
> primary key caused that `violation of 
> inserting duplicate key`.
    I 
> already used the option -O to output 
> error to a `error.log`, but it doesn`t 
> help much, because that error 
> log contains the same error message mentioned 
> above without telling me 
> exactly which record so that I can pull that 
> `duplicate record` out of 
> my import data file.
     TIA and you have a 
> great day.
David Nguyen.

----------

