I have a code which does insert into destination table from source, but at times we are ending up with deadlocks.
Due to deadlock , I have to rerun this code again.
while i do re-run , I end up with Primary key violation. Hence I want to add a check to insert only those rows which doesn't exist.
Can some help me with Tsql for this please
declare @batchSize int = 1000 INSERT INTO [dbo].[Destinationtable] ([PacketId] ,[DeviceId] ,[PacketDate] ,[PacketNumber] ,[Processed] ,[CreateDate] ,[NewReadingId]) SELECT top (@batchSize) [PacketId] ,[DeviceId] ,[PacketDate] ,[PacketNumber] ,[Processed] ,[CreateDate] ,newid() FROM [dbo].[SourceTable] where Processed is null order by CreateDate
Error message when i run after deadlock
Violation of PRIMARY KEY constraint 'PK_packetid'. Cannot insert duplicate key in object 'dbo.destinationtable'. The duplicate key value is (1234567).