= Generate a random number in the number range =
-- Create a view
Create view myview as select Re = rand ()
-- Custom function: gets a random number within a specified range.
Create Function mydata (
@ A int,
@ B INT)
Returns decimal (38,0)
As
Begin
Declare @ r decimal (38,0)
Select @ r = cast (Re * (@ B-@ A) + @ A as decimal (38,0) from myview
Return (@ r)
End
Go
-- Call (you can specify the data range as needed)
Select user_no, DBO. mydata (1000,9999) number from Table1
-- You can add a column number based on your original query. If no column is added,
-- Put the above result into a temporary table # A, and then update
-- Example:
Update Table1 set number1 = A. Number from # A, Table1 B where a. user_no = B. user_no
========================================================== =
= Generate date range random date =
Select cast (DBO. mydata (1993,1985) as nvarchar (4) + '-' + Cast (DBO. mydata () as nvarchar (2) + '-' + Cast (DBO. mydata (30,1) as nvarchar (2 ))
Select dateadd (DD, datediff (DD, '2017-05-01 ', '2017-06-30') * rand (), '2017-05-01 ')
Change to start date
To end date