asked on

Scramble/Mixup SQL Records

Hi all,

I have been asked by the developers of our internal database program for a backup of our SQL database because they "lost" their copy of it. My bosses agreed to give them a copy of it but they worry what their true intentions are. I tried to get them to accept a small portion of it but they say they want the full copy to do stress testing, or so they claim. If we give them a shorter version, we worry that the service they provide will decline further in quality.

Any ideas as to how I can go about scrambling or mixing up the data? I realize this is a broad question since you don't know what my database looks like. There are too many tables to list and the relationships between tables are numerous. I would just like to hear people's thoughts on this, maybe there is an easy way I'm missing.

I can post some exmaple of the tables I would like to scramble if necessary.

Thanks,
Primus

Emes

What I have done in the past is make there copy of the database. That will be use by the Dev team.

Then run some sql that will change important data fields like salary.

using the RAND() funciton.

then backup that data and give it to them

SOLUTION

rgc6789

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PrimusPilus

ASKER

Hi guys,

Thanks for your input!

I'm not sure if Rand will do what I need? Below is an example (short example) of what I would like to change:

PeopleID FirstName LastName Phone1 Phone2 Phone3

090909 Primus Pilus 416-555-5555 416-555-3333 416-555-4522
080808 Gaius Marius 416-555-6666 416-555-4444 416-555-2332
070707 Pompey Great 416-555-3333 416-555-2322 416-555-3421

So what I would like to do is have the entries in Phone1, Phone2, Phone3 mix matched so that Marius's numbers are now Pompey's numbers. I would also like to change the names around so that the names no longer match the PeopleID. This is because the database has a lot of tables that use the PeopleID as the key. Changing this around basically makes all the other data useless!

Am I able to use RAND to generate random phone numbers?

Thanks,
Primus

ASKER CERTIFIED SOLUTION

Emes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Emes

here is a better code

Will take care of 2 digit numbers

select
convert(char(1),convert(int,rand()*8)+2)+
convert(char(1),convert(int,rand()*10))+
convert(char(1),convert(int,rand()*10))+
'-' +
convert(char(1),convert(int,rand()*10))+
convert(char(1),convert(int,rand()*10))+
convert(char(1),convert(int,rand()*10))+
convert(char(1),convert(int,rand()*10))

You can do the same if you need an area code

PrimusPilus

ASKER

Thanks guys,

I ended up using RAND() and got most of the numbers randomly generated. The rest I simply duplicated!

Primus