update table with random values

Question

I have two tables, lets say Table1(Col1,Col2,Col3,Col4) and Table2(Col1).

I want to update some cols in Table1.

For each row in Table1 where Col1 ends with '001' (assume all values are at least length 4) I want to:

1.place a random number from the set (10,20,30,40,50,60,70,80,90) in Col2.

2.place a random 9 digit number in Col3.

3.place a random value from Table2 Col1 in Table1 Col4.

How can I do this?

Thanks!

Answer 1

I think this can get you started on the right road.

Build a table of sample data with incrementing numbers that suit your needs. You can use a loop, but a tally table or a recursive CTE will be much faster if you're doing many rows. Then, update the appropriate columns of Table1 with sample data ORDER BY NEWID(). This will randomize the order of the select-set.

UPDATE Table1
SET  Col1 = (SELECT TOP 1 RandomColumn1
             FROM   SampleData
             ORDER BY NEWID())

You may also see if RedGate's Data Generator will work for you. I have it, but have never used it.

Answer 2

I tend to use something like this:

WITH SampleData (entity_number, entity_name)
     AS (
         ... row constructors here...
        ),
     SampleDataOrdered (order_col, entity_number, entity_name)
     AS (
         SELECT ROW_NUMBER() OVER(ORDER BY NEWID()), 
                S1.entity_number, S1.entity_name
           FROM SampleData AS S1
        )
MERGE INTO MyBaseTable...;

Answer 3

CREATE TABLE MyTable( RowID int IDENTITY(1, 1),
Col1 int, Col2 int, Col3 int, Col4 int. )

DECLARE @RowCount int,
@numberRecords int

select @NumberRecords = count(*) from mytable
SET @RowCount = 1

WHILE @RowCount <= @NumberRecords
BEGIN

UPDATE MyTable
 SET  Col1 = (SELECT TOP 1 RandomColumn1 
         FROM   SampleData 
         ORDER BY NEWID()) 

 WHERE RowID = @RowCount


 SET @RowCount = @RowCount + 1
END

Hope that is some help.. an expansion of my answer.. as you can see I create your table but add a row-id column that increments.. i then create a loop that runs the update statment on a row per row basis.

Very similar to how a cursor would function but hopefully quicker.

Answer 4

I prefer to always use a set based solution. This is the set based solution I developed. I tested it and it works.

update Table1
set Col2 = (Cast(CAST(newid() AS binary(1)) AS int) % 9 + 1) *10
where substring(Table1.Col1,len(Table1.Col1)-2,3) = '001'

update Table1
set Col3 = Cast(CAST(newid() AS binary(3)) AS int)
where substring(Table1.Col1,len(Table1.Col1)-2,3) = '001'

create table #temp1(tCol1 varchar(50), tCol2 varchar(10),randomnum varchar(max) )
insert into #temp1
         select Table1.Col1,Table2.Col1,newid() as random1
         from Table1 cross join Table2 
         where substring(Table1.Col1,len(Table1.Col1)-2,3) = '001'
update Table1
set Table1.Col4 = tCol2
from
(select tCol1,tCol2,a.randomnum from 
(select max(randomnum) as randomnum from #temp1 group by tCol1) a 
left join #temp1 on a.randomnum = #temp1.randomnum) b inner join Table1 on b.tCol1 = Table1.Col1
drop table #temp1