Re: how to scramble data in a table

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 02/16/04 

Date: Sun, 15 Feb 2004 23:43:04 -0600

Sorry, I misread your post. Hopefully, Steve's answer is more help. 

-- 
Dan Guzman
SQL Server MVP
"SND" <sndip@cox.net> wrote in message news:G0YXb.1489$Mf.977@okepread04...
> This won't work in my case since I have a million records and I would have
> to build a very large dynamic Case statement.  Sorry for making it
confusing
> with my sample data.
>
> "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
> news:Oi1hirD9DHA.2672@TK2MSFTNGP10.phx.gbl...
> > If you don't mind using modulo on PersonID instead of RAND, you can
> > accomplish this with set-based processing using CASE expressions.
> >
> > UPDATE Person
> > SET
> >     LastName = CASE PersonID % 3
> >         WHEN 0 THEN 'Smith'
> >         WHEN 1 THEN 'Adams'
> >         WHEN 2 THEN 'Doe'
> >     END,
> >     FirstName = CASE PersonID % 3
> >         WHEN 0 THEN 'John'
> >         WHEN 1 THEN 'Greg'
> >         WHEN 2 THEN 'John'
> >     END,
> >     MiddleName = CASE PersonID % 3
> >         WHEN 0 THEN 'D'
> >         WHEN 1 THEN 'B'
> >         WHEN 2 THEN 'C'
> >     END
> >
> > -- 
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "SND" <sndip@cox.net> wrote in message
> news:d4WXb.1477$Mf.1107@okepread04...
> > > Does anyone have a neat way to scramble data in one table?
> > >
> > > I have a Person table with fields (PersonID, LastName, FirstName,
> > > MiddleName).  PersonID is a sequential number from 1 - 1,000,000 and
> there
> > > are NO gaps in the sequence.  I need the last names to be scrambled
with
> > > last names and so forth.  ie.  Smith, John D. could become Adams, Greg
> B.
> > >
> > > The only way I know how to accomplish this is by looping through each
> > Person
> > > record and then select 3 random PersonID using the RAND() function and
> > then
> > > update the current PersonID with the LastName, FirstName, and Middle
> names
> > > of the rand() persons.  This process works but, since I have a million
> > > records, it takes my server close to 30 minutes to process.
> > >
> > > Does anyone know of a faster process to accomplish this?
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Status Column in sysobjects table...SQL2000
    ... Please post a sample proc that demonstrates the ... >> Dan Guzman ... >> SQL Server MVP ... even though there are no compilation errors. ...
    (microsoft.public.sqlserver.programming)
  • Re: Status Column in sysobjects table...SQL2000
    ... The QUOTED_IDENTIFER setting may or may not prevent the proc from being ... > Quoted_Identifiers and/or ANSI_NULLS it would not even compile. ... >> Dan Guzman ... >> SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Database recovery with data file only
    ... Basically using that command breaks your business logic as there's no ... "Dan Guzman" wrote in message ... >> database Backup as well as ... >>> SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Read log files
    ... for ad-hoc analysis. ... >> Dan Guzman ... >> SQL Server MVP ... can't we read the log file without this. ...
    (microsoft.public.sqlserver.server)
  • Re: Looping through string and spilling them up
    ... There is an excellent discussion of a number of methods to accomplish this ... Jacco Schalkwijk ... SQL Server MVP ... "aussie rules" wrote in message ...
    (microsoft.public.sqlserver.programming)