Shuffle Names in User table
Hello experts,
I have two servers: a production server and a demo server. The demo server has a copy of the production server database. Both are running Win2003, SQL2000 SP4, and ASP pages. I would like to show people my ASP pages on my demo server without exposing real peoples name. To do this, I would like to shuffle my Users table. For example, if tbl_Users has this:
First_Name Last_Name
------------- -------------
John Doe
Sally Smith
Fred Johnson
I would like to have a SQL (or ASP) script which I can run to shuffle the names so it'll be something like this:
First_Name Last_Name
------------- -------------
John Smith
Sally Johnson
Fred Doe
This script will only need to be run once every time I restore a backup from the production server to the demo server.
Thanks in advance.
I have two servers: a production server and a demo server. The demo server has a copy of the production server database. Both are running Win2003, SQL2000 SP4, and ASP pages. I would like to show people my ASP pages on my demo server without exposing real peoples name. To do this, I would like to shuffle my Users table. For example, if tbl_Users has this:
First_Name Last_Name
------------- -------------
John Doe
Sally Smith
Fred Johnson
I would like to have a SQL (or ASP) script which I can run to shuffle the names so it'll be something like this:
First_Name Last_Name
------------- -------------
John Smith
Sally Johnson
Fred Doe
This script will only need to be run once every time I restore a backup from the production server to the demo server.
Thanks in advance.
ASKER
Mr_Peerapol, thanks for you quick response. However, that code made all of their last names the same. Also I tested it with first name field and got same results. This was not quite the effect I was looking for. Any other suggestions?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Mr_Peerapol,
This looks better...however, what do I do if my table has over 20 different columns instead of just first/last name? Is there is a quick way or will I have to write out each column name in the insert statement?
This looks better...however, what do I do if my table has over 20 different columns instead of just first/last name? Is there is a quick way or will I have to write out each column name in the insert statement?
If they are all string, you could write a function which randomly generates a string and assign the value to your columns.
If you really want to shuffle all columns, you will need to go through each column.
I think only first name and last name are enough for hiding real identity.
If you really want to shuffle all columns, you will need to go through each column.
I think only first name and last name are enough for hiding real identity.
Hi Shiseiryu1 ,
Select First_Name, (select top 1 lastname from yourtable order by newid())
from yourtable
Select First_Name, (select top 1 lastname from yourtable order by newid())
from yourtable
ASKER
Just a note Mr_Peerapol,
When I did this first part:
SELECT IDENTITY(INT, 1, 1) AS RowNo, * INTO #temp1 FROM tbl_Users ORDER BY NEWID()
SELECT IDENTITY(INT, 1, 1) AS RowNo, * INTO #temp2 FROM tbl_Users ORDER BY NEWID()
both temp tables were still in the same order. I ended up ordering by something different to make the suffle work. However, I wish I could have done it a more random way. Do you have any idea why the newID() didn't order them differently?
Thanks for your help.
When I did this first part:
SELECT IDENTITY(INT, 1, 1) AS RowNo, * INTO #temp1 FROM tbl_Users ORDER BY NEWID()
SELECT IDENTITY(INT, 1, 1) AS RowNo, * INTO #temp2 FROM tbl_Users ORDER BY NEWID()
both temp tables were still in the same order. I ended up ordering by something different to make the suffle work. However, I wish I could have done it a more random way. Do you have any idea why the newID() didn't order them differently?
Thanks for your help.
SET Last_Name = (SELECT TOP 1 Last_Name FROM tbl_Users ORDER BY NEWID())