question

rehaan avatar image
rehaan asked

Capture Primary key violation error and send an alert

If a primary key column gets a duplicate column from insert statement. How to capture it as an event and send email alert. Violation of PRIMARY KEY constraint 'PK_Transferdata'. Cannot insert duplicate key in object 'dbo.Transferdata'. The duplicate key value is (17600123).

primary-keyerror-handlingalerts
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

declare @batchSize int = 1000 INSERT INTO [dbo].[TransferData] ([PacketId] ,[DeviceId] ,[PacketDate] ,[PacketNumber] ,[Processed] ,[CreateDate] ,[NewReadingId]) SELECT top (@batchSize) [PacketId] ,[DeviceId] ,[PacketDate] ,[PacketNumber] ,[Processed] ,[CreateDate] ,newid() FROM [dbo].[TransferPacket] where Processed is null order by CreateDate This is my sql statement. As this inserts 1000 rows, how can add a if statement ? can you please suggest and help with some sql

0 Likes 0 ·

1 Answer

·
WRBI avatar image
WRBI answered

Hi @rehaan,

Are you using TRY/CATCH blocks in your code? MSDN - Reference for TRY/CATCH.

They would allow you to capture the error and do whatever you want with it i.e. write it to your custom error logs and then email it out (it's what I prefer and what we've done at places I've worked before).

Regards,

Dave

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hi @WRBI

Actually a small change ,

I want to do a check whether packetid exists in transferdata table before i do insert into it so that i avoid primary key violation.

But i am doing insert in batches like 1000, so how can i achieve this ?

0 Likes 0 ·

Thank you @WRBI for your help

1 Like 1 ·

One option that might work would be something like:

INSERT INTO dbo.TransferData (
    Column1, 
    Column2
)
SELECT
    Column1, 
    Column2
FROM 
    DataSource as ds
LEFT JOIN dbo.TransferData as td on ds.packetid = ts.packetid
WHERE td.packetid is null;

This would still be a set based approach for the 1000 rows at a time.

You could also look at using IN/NOT IN and EXCEPT I think would also work.

Have a play around and see which one your prefer or generates the best query plan/speed for you.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.