CODEWITHSUNDEEP
sqlsql-servert-sqlrandom
kralco626
kralco626

Reputation: 8624

update table with random values

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!

Upvotes: 2

Views: 10119

Answers (5)

Wes Price
Wes Price

Reputation: 346

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.

Upvotes: 2

kralco626
kralco626

Reputation: 8624

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

Upvotes: 0

Hari Tallam
Hari Tallam

Reputation: 78

I would rather prefer to run the below code and wait for sometime to run and update random values

while 1=1
BEGIN
    UPDATE top (10) a
     SET  value =rand() * 100
    from (select top 10 * from 
            MyTable
     order by newid()
     ) a
END

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

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...;

Upvotes: -1

Brad
Brad

Reputation: 15567

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.

Upvotes: 1

Related Questions