Update a SQL table with random values from another table (no join condition) -

Update a SQL table with random values from another table (no join condition) -

i'm working on script anonymize table patient data. generated table containing 50,000 rows of anonymous data.

what need number of columns in patient table updated info generated table.

ofcourse read updating tabels , how select random row table. can't figure out how combine in 1 query.

i've seen cte possible solution, don't understand how works. 1 of main issues have generated table doesn't have key in , if did shouldn't relevant since want iterate rows of patient table updating values random row generated table.

i have following:

update patients set patients.pat_firstname = fn.givenname, pat_lastname = fn.surname, pat_streetname = fn.streetaddress, pat_postalcode = fn.zipcode, pat_city = fn.city, pat_dateofbirth = fn.birthday, ( select top 1, givenname, surname, streetaddress, zipcode, city, birthday fakenamegenerator tablesample(1000 rows)) fn

executes 'random' 1 time fill every row in patient table same values. said before, can (should be??) solved cte (tally?) tables, how?

i'm close grabbing c# , code darn thing...

another way of doing add together contiguous numeric column fakenamegenerator table

alter table fakenamegenerator add together id int not null identity(1,1) create unique nonclustered index ix on fakenamegenerator(id)

then becomes problem of generating random number between 1 , 50,000

update p set p.pat_firstname = f.givenname /*...*/ patients p inner loop bring together fakenamegenerator f on f.id = (1 + abs(crypt_gen_random(8)%50000))

the inner loop join hint enforces nested loops bring together patients driving table. seeks fakenamegenerator each row re-evaluating id seek on.

sql random common-table-expression