How to update a field with random data?

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

How about this:

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

Answer 6

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

Answer 7

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.

Answer 8

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.