Get count while insert is still running

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.

more ▼

asked Jul 26, 2011 at 09:34 AM in Default

avatar image

262 3 6 9

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

@homebrew01 With the code as written, yes, you should expect to get a result with or without a NOLOCK hint.

more ▼

answered Jul 26, 2011 at 10:59 AM

avatar image

20.4k 3 7 29

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 26, 2011 at 12:38 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Pavel is completely right. Just let me add the obligatory note that using NoLock runs the risk of a dirty read. Of course, if all you are trying to do is get a status indicator, then that doesn't really matter. I personally use a technique like that as a status tracker all the time on large inserts.

Jul 26, 2011 at 04:16 PM TimothyAWiseman

Exactly @TimothyAWiseman. For seeing status and progress without any blocking the NOLOCK hit is excellent. I'm also using this approach for large inserts to see, that everything is running correctly and the inserts are not being blocked by some kind of lock. Also using this approach its possible to estimate the remaining time to finish large insert.

Jul 26, 2011 at 11:07 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 26, 2011 at 09:34 AM

Seen: 3188 times

Last Updated: Jul 26, 2011 at 11:43 AM

Copyright 2018 Redgate Software. Privacy Policy