Inserting large number of records without locking the table
<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>