Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: SQLSTATE = 23000 error

    Hi Everyone,

    I am currently working on a maintenance of a Powerbuilder App using MS SQL.

    Here is the scenario, this module uploads one of more text files and checks on duplicate entries. In usual file uploads it will do fine, store the duplicates in the database and to a text file. But in some cases, it just skips the text file encountering "SQLSTATE = 23000 Microsoft OLE DB Provider for SQL Server Violation of PRIMARY KEY constraint".

    I was asked to do to make this error go away (if possible) and have the valid and duplicate contents stored in the database as it normally should.

    I believe this happens whenever a valid row already existed in the database.
    Table_Serials for example, gets data for 'serials' from table_template. table_template has seq_no as unique identifier. but once column 'serials' is inserted to Table_Serials, it will become the primary key. So that is why this error shows up.

    I thought about creating a temp table as staging area to separate immediately the duplicates for this matter, then once it is filtered it will do the usual process. But not sure if this will work.

    Can someone please suggest a more cost effective solution for this?

    Thanks in advance.

    furei

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I thought about creating a temp table as staging area to separate immediately the duplicates for this matter, then once it is filtered it will do the usual process.
    That's the way to go. Don't define PK's in the staging table.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2011
    Posts
    5
    I already did this but it still stops to the same SQL error code. Is there a way to have the remaining dups inserted to another table automatically if that error shows up?

    thanks in advance.

    furei

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Write the SQL script to identify the records with the same PK in the staging table compared to the final table (INNER JOIN).
    Alter your script, so it inserts them into the "duplicates" table.
    Alter your script, so it deletes those from the staging table.
    Now copy the records from the staging table to the final table.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    When the staging table itself may contain duplicate records (with same PK), you could also write a script to identify those records, add them to the "duplicates" table and add DISTINCT to the script that inserts the records from the staging table into the final table.

    Don't forget to do a TRUNCATE TABLE StagingTable before each and every batch.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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