I'm running something like this:
waitfor delay '00:00:05'
insert top (1000) into TableB Select * from TableA A where A.ID not in (select ID from TableB) -- not already inserted
if @@rowcount > 0 goto more
Assuming there are 100,000 records to be inserted (100 loops), should I expect to get a result when it's halfway through if I run:
select count(*) from TableB with (nolock)
Or will my count be 0 until it completes ? It's been running 2 hours and count is still 0 ! (No blocking processes)
Update: I cancelled the insert, modified some code, and now it seems to be working .... basically: inefficient code.
As @Oleg stated in comment you should receive count whenever you use the NOLOCK hint or not.
In case you use the NOLOCK hint you can receive partial counts even during the insertion of the records.
Without the NOLOCK hit, you will receive counts only after each batch of 1000 records as the records being inserted will be in not committed transaction and eventually locks will be placed on the table/index which will block the count until the current insert batch finish.
answered Jul 26, 2011 at 12:38 PM