August 12, 2008 at 9:12 am
Here is my query:
I have a table which consists of confidential information such as SSN and Name. I want to create a dummy database with dummy SSNs. My table consists of 500 records. How do I run a script to replace the actual SSNs with dummy SSNs. I know I can create a random variable. But I need to integrate this table with another table as I plan to integrate them together. I want to create dummy variables such as 111111111, 222222222 so that I can match with the other database. Or I would like to create a variable such as 111111111 + 1, and so on and then update the table. Can someone tell me how this can be done ? I am a newbie to SQL and SQL Server.
Thanks
August 12, 2008 at 10:20 am
You probably want to use a substitution table,
Like this:
--====== Create a substitution table:
Create Table #SSNSubs(
SSN int,
Subs int,
CONSTRAINT PK_SSNSubs_1
PRIMARY KEY CLUSTERED (SSN))
GO
--====== Get the existing SSN's and make the substitutes
INSERT into #SSNsubs
Select SSN, rand() * 999999999
From Table1
--Note: this assumes that SSN is unique in Table1
--====== Update the values in the tables
Update T
Set SSN = S.Subs
From Table1 as T
Join #SSNSubs as S ON T.SSn = S.SSN
Update T
Set SSN = S.Subs
From Table2 as T
Join #SSNSubs as S ON T.SSn = S.SSN
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply