SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get count while insert is still running


Get count while insert is still running

Author
Message
homebrew01
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5025 Visits: 9108
I'm running something like this:


more:

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 ?

Update: I cancelled the insert, modified some code, and now it seems to be working .... basically: inefficient code.



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30055 Visits: 9671
You'll see it as it inserts.

Not 100% sure you'll get any numbers like 16472 instead of multiples of 1000. But that might be a fun test to setup.


Care to do it and post the results?
homebrew01
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5025 Visits: 9108
It's been running 2 hours and I get 0 Sad



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30055 Visits: 9671
homebrew01 (7/26/2011)
It's been running 2 hours and I get 0 Sad


What is it waiting on? (sp_WhoIsActive)
homebrew01
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5025 Visits: 9108
I have a process to alert me if there's blocking, but no problems so far.



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30055 Visits: 9671
Don't know what to tell you. I've definitely done this in the past and I was getting ever increasing counts in the table. I don't know what could be the difference (except that you're looking in the wrong table but I don't dare to suggest that option :w00tSmile
homebrew01
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5025 Visits: 9108
Ninja's_RGR'us (7/26/2011)
Don't know what to tell you. I've definitely done this in the past and I was getting ever increasing counts in the table. I don't know what could be the difference (except that you're looking in the wrong table but I don't dare to suggest that option :w00tSmile


I thought I've done it in the past too .... I'm checking the right table .... could be some other brain fart going on with the where clause.



homebrew01
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5025 Visits: 9108
Update: I cancelled the insert, modified some code, and now it seems to be working .... basically: inefficient code.



Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30055 Visits: 9671
Happy to almost have helped Hehe.
homebrew01
homebrew01
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5025 Visits: 9108
Thanks :-)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search