Posted by Chris Johnson on 11/02/05 19:49

Here is my dilema. I have a 120 GB database that I need to mask customer
credit card numbers in. The field is a varchar (16). I need to update
the field so that we only store the first 4 numbers and the last 4
numbers of the credit card and insert * to fill in the rest of the
credit card number.

I was going to do this as a loop using the following code:

While Exists (Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)
Begin
Begin Transaction T1
Update
Header
Set
Header.CCNbr = Left (D1.CCNbr, 4) + '********' + Right (D1.CCNbr, 4)
From
(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1) as D1
Commit Transaction T1

If Not Exists(Select Top 10 * From Header Where IsNumeric(CCNbr) = 1)
Break
Else
Continue
End

In theory this only selects the top 10 rows, updates them, dumps the log
and moves on to the next 10 until all the rows are updated.

I tried running this on my test database and it fills up the transaction
log.

Can anyone tell me the best way to go about doing what I need?

Thanks


*** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]