CODEWITHSUNDEEP
sqlsql-server
Dan Williams
Dan Williams

Reputation: 4950

How to update a field with random data?

I've got a new varchar(10) field in a database with 1000+ records. I'd like to update the table so I can have random data in the field. I'm looking for a SQL solution.

I know I can use a cursor, but that seems inelegant.

MS-SQL 2000,BTW

Upvotes: 0

Views: 3790

Answers (8)

tzot
tzot

Reputation: 96081

Something like (untested code):

UPDATE yourtable
SET yourfield= CHAR(32+ROUND(RAND()*95,0));

Obviously, concatenate more random characters if you want up to ten chars. It's possible that the query optimizer might set all fields to the same value; in that case, I would try

SET yourfield=LEFT(yourfield,0)+CHAR…

to trick the optimizer into recalculating each time the expression.

Upvotes: 1

theo
theo

Reputation: 8921

if you are in SQL Server you can use 

CAST(RAND() as varchar(10))

EDIT: This will only work inside an iteration. As part of a multi-row insert it will use the same RAND() result for each row.

Upvotes: 0

Andy M
Andy M

Reputation:

Why not use the first 10 characters of an md5 checksum of the current timestamp and a random number?

Upvotes: 1

Stephen Wrighton
Stephen Wrighton

Reputation: 37880

How about this:

UPDATE TBL SET Field = LEFT( CONVERT(varchar(255), @myid),10)

Upvotes: 0

James Curran
James Curran

Reputation: 103605

update MyTable Set RandomFld =  CONVERT(varchar(10), NEWID())

Upvotes: 5

Mitchel Sellers
Mitchel Sellers

Reputation: 63136

Additionally, if you are just doing this for testing or one time use I would say that an elegant solution is not really necessary.

Upvotes: 1

Jeremy Coenen
Jeremy Coenen

Reputation: 1075

If this is a one time thing just to get data into the system I really see no issue with using a cursor as much as I hate cursors they do have their place.

Upvotes: 0

Mitchel Sellers
Mitchel Sellers

Reputation: 63136

You might be able to adapt something like this to load a test dataset of values, depending on what you are looking for

Upvotes: 1

Related Questions