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).
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
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
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 ?
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.
18 People are following this question.
