How can I fill a column with random numbers in SQL? I get the same value in every row

by

Instead of rand(), use newid(), which is recalculated for each row in the result. The usual way is to use the modulo of the checksum. Note that checksum(newid()) can produce -2,147,483,648 and cause integer overflow on abs(), so we need to use modulo on the checksum return value before converting it to absolute value.

UPDATE CattleProds
SET    SheepTherapy = abs(checksum(NewId()) % 10000)
WHERE  SheepTherapy IS NULL

This generates a random number between 0 and 9999.

More Related Contents:

  1. Max Nbr of Months W/ No Activity in Past 12 Months
  2. Export from SQL Server to Excel
  3. hide/remove header and footer
  4. How do I generate a random number for each row in a T-SQL select?
  5. Combine multiple results in a subquery into a single comma-separated value
  6. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  7. SQL Server: SELECT only the rows with MAX(DATE)
  8. Floor a date in SQL server
  9. LEN function not including trailing spaces in SQL Server
  10. How FOR XML PATH(”) works when concatenating rows
  11. How to view transaction logs in SQL Server 2008 [closed]
  12. Scheduled run of stored procedure on SQL server
  13. SQL Server ORDER BY date and nulls last
  14. SSRS 2014 report deploy issue
  15. Best way to implement an audit trail in SQL Server?
  16. Set start value for column with autoincrement
  17. How can I change from SQL Server Windows mode to mixed mode (SQL Server 2008)?
  18. SqlDataAdapter.Fill method slow
  19. syntax for single row MERGE / upsert in SQL Server
  20. How to find all the dependencies of a table in sql server
  21. What happens to the primary key Id when it goes over the limit?
  22. Will GETUTCDATE() return the same value if used twice in the same statement?
  23. Convert decimal time to hours and minutes
  24. Create table (structure) from existing table
  25. Importing excel files having variable headers
  26. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  27. SQL SERVER – Understanding how MIN(text) works
  28. Pivot multiple columns based on one column in SQL Server
  29. How to convert text column to datetime in SQL
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment