I have a table with the following structure :
CREATE TABLE guest.GangulyMktData
symbol char(255) NOT NULL,
stamp char(255) NOT NULL,
bid char(255) NULL,
bqty char(255) NULL,
ask char(255) NULL,
aqty char(255) NULL,
UNIQUE NONCLUSTERED (stamp,symbol)
IF OBJECT_ID('guest.GangulyMktData') IS NOT NULL
PRINT '<<< CREATED TABLE guest.GangulyMktData >>>'
PRINT '<<< FAILED CREATING TABLE guest.GangulyMktData >>>'
Now, I want to bcp data into this table and am encountering the following message :
Msg 2601, Level 14, State 3:
Attempt to insert duplicate key row in object 'GangulyMktData' with
unique index 'Dupe'
bcp copy in failed
I want this to happen as I have set constraints on the table. But, aren't the rows that dont violate this constraint supposed to get copied without error ? Or does bcp abort on the first error itself ?
Also, would "CONSTRAINT Dupe
UNIQUE NONCLUSTERED (stamp,symbol) " create a composite key constraint for me or am I wrong there ? - Essentially I want any inserts with duplicate values in stamp AND symbol to kick out.
But, this is taking way too much time as expected. And Im dealing with huge amt of data. ( My DBAs cut me out while half way thro the process :- (( )
Looks like I might have to drop the unique indexes from my table and make it eat all the data first - before maybe run a query to drop the dupes from the table.
but thanks anyway ..
Originally posted by sybase_user2003
If you want the bcp to be done partially, you can use the -b option.
bcp .... -b 1
Then you do the insert in batches of one row. If one batch fails, the process continues with the next row.
but then, if i drop the indixes, i'll have dupes in the table wont I ? - i was trying to avoid that ..
but thanks .. i did quite a lot of research on the topic and am wiser now ..
Originally posted by MattR
Well if you have indexes on a table you're using 'slow' BCP because the indexes have to be updated with each insert. It's common practice to drop the indexes, load, then re-create them.