Efficient way to insert rows in a table busy with deletes/updates in SQL Server
<p>Here is my question: I have 10,000 rows to be inserted into <code>TableX</code>. This table is always busy with lots of deletes and updates done by many concurrent users. I am thinking to insert the rows in chunks something like mentioned below:</p>

<pre><code>Declare @i int = 1

while @i &gt; 0
begin
   begin transaction
       insert top (1000) id, col1, col2, col3 
       into TableX
           select id, col1, col2, col3 
           from TableY as a
           where not exists (select 1 from TableX as b where a.id = b.id)
       set @i = @@rowcount

   commit transaction
end
</code></pre>

<p>Now one of the problem I can think of is. If I insert a record and commit it while delete is going on in another connection. There is a chance that my inserted row gets deleted if my record comes in the where condition of delete statement. I guess I will not be the reason for blocking for other users. We are using read committed isolation level.</p>

<p>So my question is should I go ahead and insert these rows or there is something I need to worry about?</p>

<p>Here is DDL statement for TableX and TableY</p>

<pre><code>create tableX
(id int primary key,
col1 varchar(100),
col2 varchar(100),
col3 varchar(100)
)
create nonclustered index ncli on TableX(cl1) include (col2,col3)

create tableY
(id int primary key,
col1 varchar(100),
col2 varchar(100),
col3 varchar(100)
)
create nonclustered index ncli on TableY(cl1) include (col2,col3)
</code></pre>

<p>Thanks ...</p>