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)
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 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