While loop - out of memory error

Question

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)

BEGIN

    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

    BEGIN

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

    END



    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

END

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!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

While loop - out of memory error

Sign up