|
|
Posted by Erland Sommarskog on 11/03/05 01:13
Chris Johnson (cjohnson@cskauto.com) writes:
> 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.
So what recovery mode do you have? If you have full or bulk-logged
recovery, batching does not help, if you don't every now and then
back up the transaction log. If you have simple recovery mode, SQL Server
truncates the log often.
Normally, a production database has full recovery. However, for a wild
operation like this one, it's reasonable to switch to simple mode, do
the operation, change back, and then take a full backup.
Now to your Update statement. Since you have two tables and no restriction
on Header, your updating each row in Header each time. And you are updating
it ten times...
Here is a better way to do it. I've increased the batch size, since ten is
far too low.
SET ROWCOUNT 100000
WHILE 1 = 1
BEGIN
UPDATE Header
SET CCNbr = Left (CCNbr, 4) + '********' + Right (CCNbr, 4)
WHERE CCNbr NOT LIKE '%*%'
SELECT @err = @@error, @rowc = @@rowcount
IF @err <> OR @rowc < 100000
BREAK
END
SET ROWCOUNT 0
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|