Geek Answers Handbook

Detailed error message for violation of primary key constraint in sql2008?

I am inserting a large number of rows into an empty table with a primary key constraint in one column. If there is a repeated key error, is there a way to find out the value of the key (or string) that caused the error?

Checking data before inserting, unfortunately, is not something I can do right now.

Using SQL 2008.

Thanks!

Doing count (*) / group by thing is what I am trying to avoid, it is an insertion of hundreds of millions of rows from hundreds of different databases (some of which are located on remote servers) ... I do not have time or place to do the insertion twice.

The data should be unique to suppliers, but, unfortunately, their verification does not work correctly in 100% of cases, and I'm trying to at least see where this happens, so I can help them troubleshoot.

Thanks!

+4
source share
5 answers

There is no way to do this that will not slow down your process, but here is one way that will facilitate it. You can add a trigger instead to this table for insertions and updates. The trigger checks each entry before inserting it and ensures that it does not cause a primary key violation. You can even create a second table to detect violations and have a different primary key (for example, an identification field) on this, and the trigger will insert rows into your table with errors.

Here is an example of how a trigger can work:

CREATE TRIGGER mytrigger ON sometable INSTEAD OF INSERT AS BEGIN INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted; INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted; END

In this example, I check the field to make sure it is numeric before inserting data into the table. You will need to modify this code to check for primary key violations, for example, you can join the INSERTED table in your existing table and only insert rows in which you cannot find a match.

+3
source

The decision will depend on how often this happens. If he is less than 10% of the time, I would do the following:

  • Insert data
  • If the error repeats the Bravax solution (remove the restriction, insert, find dup, report and kill dup, enable the restriction).

This means that it will cost you only a few times when an error occurs.

If this happens more often, I would look at sending boys to see suppliers :-)

+1
source

Corrected version:
Since you do not want to embed twice, you could:

 Drop the primary key constraint. Insert all data into the table Find any duplicates, and remove them Then re-add the primary key constraint

Previous answer: Insert data in a duplicate table without primary key constraint.

Then run the query to determine rows that have duplicate values ​​for the rpimary key column.

 select count(*), <Primary Key> from table group by <Primary Key> having count(*) > 1
0
source

Use SSIS to import data and verify this as part of the data stream. This is the best way to handle it. SSIS can send bad records to a table (which you can later send to the provider to help them clear their action) and process good ones.

0
source

I can’t believe that SSIS doesn’t just cope with this “reality”, because, if you encounter this, often you need and want to be able to:

  • See if a record exists with a specific unique or primary key
  • If not, paste it
  • If so, either ignore it or upgrade.

I don’t understand how they would release a product from the door without this feature built into an easy-to-use way. For example, set the component attribute to automatically verify this.

0
source

More articles:


All Articles