title: Inserting large number of records without locking the table
body: <p>I am trying to insert <strong>1,500,000</strong> records into a table. Am facing table lock issues during the insertion. So I came up with the below batch insert. </p>
  
  <pre><code>DECLARE @BatchSize INT = 50000
  
  WHILE 1 = 1
    BEGIN
        INSERT INTO [dbo].[Destination] 
                    (proj_details_sid,
                     period_sid,
                     sales,
                     units)
        SELECT TOP(@BatchSize) s.proj_details_sid,
                               s.period_sid,
                               s.sales,
                               s.units
        FROM   [dbo].[SOURCE] s
        WHERE  NOT EXISTS (SELECT 1
                           FROM   dbo.Destination d
                           WHERE  d.proj_details_sid = s.proj_details_sid
                                  AND d.period_sid = s.period_sid)
  
        IF @@ROWCOUNT &lt; @BatchSize
          BREAK
    END 
  </code></pre>
  
  <p>I have a clustered Index on <code>Destination</code> table <code>(proj_details_sid ,period_sid )</code>. <code>NOT EXISTS</code> part is just to restrict inserted records from again inserting into the table </p>
  
  <p>Am I doing it right, will this avoid table lock ? or is there any better way. </p>
  
  <p><strong>Note :</strong> Time taken is more or less same with batch and without batch insert </p>