Scramble MS CRM Data

This is the SQL Query for scrambling CRM contact and account data.
Sometimes a copy of customer DB is required.You don’t wanna be hacked… and you most likely won’t be… but just in case… run the queries below..

The Contact-query updates FirstName, MiddleName, LastName and City with a random value from another record.
It looks like real data… but it isn’t.

This method is obviousely unsupported, because it’s direct SQL.
It’s not really fast either … 70.000 records took me 21 hours..

 

–Be sure int match your id column datatype

Declare@iduniqueidentifier

 

–Add a WHERE here to select just a subset of your table

DECLARE ContactCursor CURSORFORSELECTContactIdFROMContact

OPEN ContactCursor

FETCH NEXT FROM ContactCursorINTO@id;

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

–Warning: NEWID() is generated once per query, so update the fullname in two queries.

UPDATEContact

SETFirstName=(SELECTTOP 1 FirstNameFROMContactORDERBYNEWID())

WHEREContactId=@id

UPDATEContact

SETMiddleName=(SELECTTOP 1 MiddleNameFROMContactORDERBYNEWID())

WHEREContactId=@id

UPDATEContact

SETLastName=(SELECTTOP 1 LastNameFROMContactORDERBYNEWID())

WHEREContactId=@id

UPDATEContact

SETAddress1_City=(SELECTTOP 1 Address1_CityFROMContactORDERBYNEWID())

WHERE ContactId = @id

UPDATEContact

SETFullName=RTRIM(FirstName +’ ‘+MiddleName)+’ ‘+ LastName

WHEREContactId=@id

FETCH NEXT FROM ContactCursorINTO@id;

END

 

CLOSE ContactCursor;

DEALLOCATE ContactCursor;

 

 

DECLARE AccountCursor CURSOR FOR

SELECT AccountId FROM Account

OPEN AccountCursor

FETCH NEXT FROM AccountCursor INTO @id;

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT @@CURSOR_ROWS;

–Warning: NEWID() is generated once per query, so update the fullname in two queries.

UPDATE Account

SET Name = (SELECT TOP 1 FirstName FROM Contact ORDER BY NEWID())

WHERE AccountId = @id

–UPDATE Contact

— SET Address1_City = (SELECT TOP 1 Address1_City FROM Contact ORDER BY NEWID())

–WHERE ContactId = @id

FETCH NEXT FROM AccountCursor INTO @id;

END

CLOSE AccountCursor;

DEALLOCATE AccountCursor;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s