Owain Esau Owain Esau - 3 years ago 128
SQL Question

While loop - out of memory error

I'm trying to create a quick script to anonymise a database by switching the names around, I am using a while loop to do this which is causing an out of memory error (it gets to around 950 records before it crashes) this is part of the code I have:

DECLARE @counter INT;
SET @counter = 1

WHILE @counter < (SELECT MAX(anonID) FROM anon_PersonChangeData)
SET @Lower = 1 ---- The lowest random number
SET @Upper = ( SELECT MAX(anonID) + 1 FROM anon_PersonChangeData ) ---- The highest random number
SET @Random = ( SELECT ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) )

WHILE @Random = @counter
SET @Random = ( SELECT ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) )

UPDATE anon_PersonChangeData
SET personNewFirstName = (SELECT personCurrentFirstName FROM anon_PersonChangeData WHERE anonID = @Random)
, personNewSurname = (SELECT personCurrentSurname FROM anon_PersonChangeData WHERE anonID = @Random)
WHERE anonID = @counter

SET @counter = @counter + 1

AnonID IS just an incremented field I am using starting from 1.

This also seems to take a very long time to get to even 950.

Any suggestions would be greatly appreciated!

Answer Source

ORDER BY NEW() will give you a random sequence. This is good enough for you?

; with cte as
    select  rn = ROW_NUMBER() over (order by newid()), *
    from    anon_PersonChangeData
update  c1
set personNewFirstName  = c2.personNewFirstName,
    personNewSurname    = c2.personNewSurname 
from    cte c1
    inner join cte c2   on  c1.rn = c2.rn

If you want random First & Surname, just inner join the cte again and update Surname from the new cte

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download