CodingPleasure

cultivate passion for everything else that goes on around programming

Scramble Data in SQL Table

 

Given a request to randomly inter-change the values of some columns in a SQL Server table, I used the following SQL script:

   1: update Table1 set Column1 = (
   2:     select top 1 Column1 FROM Table1 t1
   3:     where Table1.Id != t1.Id
   4:     order by NEWID()
   5: )

We update the table Table1 using random values from the same table. The random row is selected by using this T-SQL:

   1: select top 1 Column1 FROM Table1
   2: order by NEWID()

The where clause is used to assure that the update takes a new random row for each row that will be updated.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: