Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: bcp in doubts ..

    Hi,

    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,
    CONSTRAINT Dupe
    UNIQUE NONCLUSTERED (stamp,symbol)
    )
    LOCK ALLPAGES
    WITH MAX_ROWS_PER_PAGE=10
    go
    IF OBJECT_ID('guest.GangulyMktData') IS NOT NULL
    PRINT '<<< CREATED TABLE guest.GangulyMktData >>>'
    ELSE
    PRINT '<<< FAILED CREATING TABLE guest.GangulyMktData >>>'
    go


    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.

    Any help would be much appreciated.

    thanks ..
    sunandan.

  2. #2
    Join Date
    Sep 2003
    Posts
    17

    bcp

    Hi,

    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.

    Hope that helps.

    Regards,
    Ulrike

  3. #3
    Join Date
    Nov 2003
    Posts
    4

    Re: bcp

    Thanks Ulrike.
    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 ..
    sunandan ganguly.

    Originally posted by sybase_user2003
    Hi,

    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.

    Hope that helps.

    Regards,
    Ulrike

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Create the indexes with the "with ignore_dup_key" option. It will silently fail and should keep going.

    http://sybooks.sybase.com/onlinebook...768;pt=37768#X
    Thanks,

    Matt

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    tried that already .. but thats slow too ..

    Originally posted by MattR
    Create the indexes with the "with ignore_dup_key" option. It will silently fail and should keep going.

    http://sybooks.sybase.com/onlinebook...768;pt=37768#X

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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.
    Thanks,

    Matt

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •