0

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).​

0

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
0
0

I suspect there is something wrong with the transaction that inserts data and updates processed, they should be atomic. On error, you should rollback the transaction and be on safe ground, so you can restart the transaction.

However, you can try MERGE:

MERGE [dbo].[Destinationtable] as x
USING (
    SELECT top (@batchSize) [PacketId]
    ,[DeviceId]
    ,[PacketDate]
    ,[PacketNumber]
    ,[Processed]
    ,[CreateDate]
    ,newid()
    FROM [dbo].[SourceTable]
    where Processed is null 
    order by CreateDate
) y 
    ON x.<key> = <y.key>
WHEN NOT MATCHED THEN
    INSERT (<columns>)
    VALUES (...);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.