Detecting PK value violations in a insert batch
-
Hi everybody,
Do you know a way (Transac or something else) in which I can instruct SQLServer to provide specific information about the values that produce a PK constraint value is being violated when a batch of operation are issued?
An example to clariy ideas:
TableA: PK (ColA)
ColA ColB
------------
1 Hi
Over TableA I have an application that submmit the following opetarions as a batch:
INSERT INTO TableA(ColA,ColB) VALUES (2,'How')
INSERT INTO TableA(ColA,ColB) VALUES (3,'Are')
INSERT INTO TableA(ColA,ColB) VALUES (1,'You')
INSERT INTO TableA(ColA,ColB) VALUES (4,'Ok')
When this whole batch is executed SQLServer only returns:
Violation of PRIMARY KEY constraint 'PK__TableA__7A12F3AE'. Cannot insert duplicate key in object 'TableA'.
The statement has been terminated.
but the key here is that I would know which is the duplicate key value that generates the violation.
Any ideas???
-
I hope you can change the application, because otherwise I don't think it's possible. You didn't state whether or not rows with existing PKs should be updated, or if they should be treated as an error condition.
One way is to have the application place error checking after each INSERT statement, like this:
INSERT INTO TableA(ColA,ColB) VALUES (1,'You')
IF @@ERROR > 0
PRINT 'Value "You" with primary key 1 failed to INSERT'
Alternatively, instead of PRINTing the error, you could insert the desired data into a log/error table. Using your example:
CREATE TABLE Errors
(
eid int IDENTITY(1,1) PRIMARY KEY
, ColA int
, ColB varchar(10)
, error_dt datetime DEFAULT GetDate()
)
GO
Then, your application would create code like this:
INSERT INTO TableA(ColA,ColB) VALUES (1,'You')
IF @@ERROR > 0
INSERT Errors (ColA, ColB) VALUES (1, 'You')
A third method would be for the application to check the validity of each row before trying to do the insert.
-
Thanks very much for your help.. I was affraid of that so I´m transmiting your reply to the developers team leader.
Regards,
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply