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

That's the way to go. Don't define PK's in the staging table.

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

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.

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.