How to avoid primary key violation while doing inserts 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).​
use not exists

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  
  and not exists (select 1 from [Destinationtable] 
                  where [PacketId] = [SourceTable].[PacketId])
order by CreateDate