Need help with Mass Update Script

[JVZ]

Could you some please help me with this script.

The propose of the script is to update about 3 million rows. The problem is that the script is that it is not commiting the transaction after 50,000 updates.

here is the code, if anybody has any ideas.

--Set rowcount to 50000 to limit number of updates
--performed in each batch to 50K rows.
Set rowcount 50000

--Declare variable for row count
Declare @rc int
Set @rc=50000

While @rc=50000
 Begin

  Begin Transaction

  
  UPDATE TblCM With (tablockx, holdlock)
  set Version = 
  CASE
	WHEN [BILL ACCT NUM] = '212Y990001001' THEN
		'BABS'
	WHEN [BILL ACCT NUM] = '212Y990002002' THEN
		'BACI'
	WHEN [BILL ACCT NUM] = '212Y990004004' THEN
		'Smart Touch'
	WHEN [BILL ACCT NUM] = '212Y990006006' THEN
		'Junk'
	WHEN [BILL ACCT NUM] = '212Y990007007' THEN
		'GSI'
	WHEN [BILL ACCT NUM] = '212Y990008008' THEN
		'BABS2'
	WHEN [BILL ACCT NUM] = '212Y990019019' THEN
		'West Babs'
	WHEN [BILL ACCT NUM] = '212Y990020020' THEN
		'West Babs'
	WHEN [BILL ACCT NUM] = '212Y990022022' THEN
		'West Babs'
	WHEN [BILL ACCT NUM] = '212Y990023023' THEN
		'West Baci'
	WHEN [BILL ACCT NUM] = '212Y990025025' THEN
		'WCOM2 Enterprise'
  END
  where version is null	and ([INV DATE] = CONVERT(DATETIME, '2003-09-25 00:00:00', 102))

  --Get number of rows updated
  --Process will continue until less than 50000
  Select @rc=@@rowcount

  --Commit the transaction
  Commit
 End

Thanks for you help!

 

[JayKusch]

Try This ...

--Declare variable for row count

Declare @rc int

While @rc > 0
Begin

Begin Transaction

Set rowcount 50000

UPDATE TblCM With (tablockx, holdlock)
set Version =
CASE
WHEN [BILL ACCT NUM] = '212Y990001001' THEN
'BABS'
WHEN [BILL ACCT NUM] = '212Y990002002' THEN
'BACI'
WHEN [BILL ACCT NUM] = '212Y990004004' THEN
'Smart Touch'
WHEN [BILL ACCT NUM] = '212Y990006006' THEN
'Junk'
WHEN [BILL ACCT NUM] = '212Y990007007' THEN
'GSI'
WHEN [BILL ACCT NUM] = '212Y990008008' THEN
'BABS2'
WHEN [BILL ACCT NUM] = '212Y990019019' THEN
'West Babs'
WHEN [BILL ACCT NUM] = '212Y990020020' THEN
'West Babs'
WHEN [BILL ACCT NUM] = '212Y990022022' THEN
'West Babs'
WHEN [BILL ACCT NUM] = '212Y990023023' THEN
'West Baci'
WHEN [BILL ACCT NUM] = '212Y990025025' THEN
'WCOM2 Enterprise'
END
where version is null and ([INV DATE] = CONVERT(DATETIME, '2003-09-25 00:00:00', 102))

--Commit the transaction
Commit

--Process will continue until less than 50000

Set @rc = ( SELECT COUNT(*) FROM TblCM
WHERE version is null and ([INV DATE] = CONVERT(DATETIME, '2003-09-25 00:00:00', 102))

End


Thanks

J. Kusch