= 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