Re: Auto generating numbers

• sudrav

  SSC-Addicted

  Points: 424

  More actions

  August 12, 2008 at 9:12 am

  #93787

  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

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 12, 2008 at 10:20 am

  #856856

  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]