Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get count while insert is still running Expand / Collapse
Author
Message
Posted Tuesday, July 26, 2011 11:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 2,832, Visits: 8,507
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.



Post #1148528
Posted Tuesday, July 26, 2011 11:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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?
Post #1148534
Posted Tuesday, July 26, 2011 11:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 2,832, Visits: 8,507
It's been running 2 hours and I get 0


Post #1148539
Posted Tuesday, July 26, 2011 11:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
homebrew01 (7/26/2011)
It's been running 2 hours and I get 0


What is it waiting on? (sp_WhoIsActive)
Post #1148547
Posted Tuesday, July 26, 2011 11:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 2,832, Visits: 8,507
I have a process to alert me if there's blocking, but no problems so far.


Post #1148550
Posted Tuesday, July 26, 2011 12:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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 )
Post #1148553
Posted Tuesday, July 26, 2011 12:15 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 2,832, Visits: 8,507
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 )


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.



Post #1148561
Posted Tuesday, July 26, 2011 12:39 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 2,832, Visits: 8,507
Update: I cancelled the insert, modified some code, and now it seems to be working .... basically: inefficient code.


Post #1148579
Posted Tuesday, July 26, 2011 12:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Happy to almost have helped .
Post #1148587
Posted Tuesday, July 26, 2011 1:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 2,832, Visits: 8,507
Thanks


Post #1148596
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse