How to Insert Random Values into a SQL Server Table

SQL Server: how to insert random integers into table?

you can use select CAST(RAND() * 1000000 AS INT) AS [RandomNumber] for generating or selecting random integers .
so the full query will be something like that :

    DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum

regards..

How can I insert random values into a SQL Server table?

When the query engine sees this...

(SELECT TOP 1 [val] FROM @randomStuff ORDER BY NEWID())

... it's all like, "ooooh, a cachable scalar subquery, I'm gonna cache that!"

You need to trick the query engine into thinking it's non-cachable. jfar's answer was close, but the query engine was smart enough to see the tautalogy of MyTable.MyColumn = MyTable.MyColumn, but it ain't smart enough to see through this.

UPDATE MyTable
   SET MyColumn = (SELECT TOP 1 val
                     FROM @randomStuff r
                          INNER JOIN MyTable _MT
                                  ON M.Id = _MT.Id
                    ORDER BY NEWID())
 FROM MyTable M

By bringing in the outer table (MT) into the subquery, the query engine assumes subquery will need to be re-evaluated. Anything will work really, but I went with the (assumed) primary key of MyTable.Id since it'd be indexed and would add very little overhead.

A cursor would probably be just as fast, but is most certainly not as fun.

How do I insert random characters into a sql database column?

For MS SQL you can use NEWID and CHECKSUM functions like:

  UPDATE Users 
  SET Gender = (CASE WHEN ABS(CHECKSUM(NEWID()) % 2) = 1 THEN 'M' ELSE 'F' END)

NEWID() will generate random GUID
CHECKSUM() will generate hash of that GUID
ABS() to make it either 1 or 0

WARNING! While some people suggesting to use RAND function - please do not use it for this particular case. The query like this:

UPDATE Users SET Gender = CASE WHEN (RAND() > 0.5) THEN 'M' ELSE 'F' END

.. will result that you have all values either M or either F.

Potentially you can seed RAND function with some value like Id, but distribution of values will be not very good: like first 30-40% all M, then 30-40% all F, then M again.

How to generate random data in SQL server

It's not too difficult to generate random data, even in SQL

For example, to get a random username from your userprofile table.

BEGIN
-- get a random row from a table
DECLARE @username VARCHAR(50)
SELECT @username = [Username] FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY [Username]) [row], [Username]
    FROM [UserProfile]
) t 
WHERE t.row = 1 + (SELECT CAST(RAND() * COUNT(*) as INT) FROM [UserProfile])

print(@username)
END

To generate a random integer...

BEGIN
-- get a random integer between 3 and 7 (3 + 5 - 1)
DECLARE @totalviews INT
SELECT @totalviews = CAST(RAND() * 5 + 3 as INT)
print(@totalviews)
END

To generate a random varchar string

BEGIN
-- get a random varchar ascii char 32 to 128
DECLARE @videoname VARCHAR(160)
DECLARE @length INT
SELECT @videoname = ''
SET @length = CAST(RAND() * 160 as INT)
WHILE @length <> 0
    BEGIN
    SELECT @videoname = @videoname + CHAR(CAST(RAND() * 96 + 32 as INT))
    SET @length = @length - 1
    END
print(@videoname)
END

And finally, a random date

BEGIN
-- get a random datetime +/- 365 days
DECLARE @uploadtime DATETIME
SET @uploadtime = GETDATE() + (365 * 2 * RAND() - 365)
print(@uploadtime)
END

Insert random Data content in SQL Server 2008

;with sequence as (
    select N = row_number() over (order by @@spid)
    from sys.all_columns c1, sys.all_columns c2
)
insert into [Table] (UserID, FirstName, Lastname)
select
    'UserID' + right('000000' + cast(N as varchar(10)), 6),
    'John' + right('000000' + cast(N as varchar(10)), 6),
    'Doe' + right('000000' + cast(N as varchar(10)), 6)
from sequence where N <= 300000

MS sql insert random number set to the parameter using stored procedure

You can't use expressions as default values on a stored procedure declaration. You can, however, use them with the SET statement once inside your code.

This fails:

CREATE PROCEDURE MyProcedure
    @Parameter INT = (100 + 50) -- Incorrect syntax near '('.
AS
BEGIN

    RETURN @Parameter

END

This works:

CREATE PROCEDURE MyProcedure
    @Parameter INT = NULL
AS
BEGIN

    SET @Parameter = 100 + 50

    RETURN @Parameter

END

For your case:

CREATE PROCEDURE YourProcedure
    @DATE_CREATED datetime = null,
    @STATUS varchar(10) = 'Open',
    @CODE_GEN VARCHAR(200) = NULL
as
BEGIN

    set nocount on

    IF @CODE_GEN IS NULL
        SET @CODE_GEN = ('TN'+ SELECT LEFT(CONVERT(varchar(100), NEWID()),3))+'-'+RIGHT(CONVERT(varchar(100), NEWID()),5)

    INSERT INTO MEDREC_CODEGEN (Status,DATE_CREATED,CODE_GEN)
    values (@STATUS, COALESCE(@DATE_CREATED,GETDATE()), @CODE_GEN)

END

Insert N random values into table

No loops, one insert

;WITH cte AS
(  --there are easier ways to build a numbers table
   SELECT
       ROW_NUMBER() OVER (ORDER BY (select 0)) AS rn
   FROM
      sys.columns c1 CROSS JOIN sys.columns c2 CROSS JOIN sys.columns c3
)
INSERT INTO [T1] ([Value])
OUTPUT INSERTED.ID INTO T2  -- direct insert to T2
SELECT RAND(CHECKSUM(NEWID()))
FROM cte
WHERE rn <= @N;

Inserting random data from a list

CREATE TABLE mytable (
  id SERIAL PRIMARY KEY,
  date DATE NOT NULL,
  description TEXT,
  priority ENUM('High','Medium','Low') NOT NULL
);

INSERT INTO mytable (date, priority)
  SELECT '2019-07-01' + INTERVAL FLOOR(RAND()*365) DAY, 
      ELT(1+FLOOR(RAND()*3), 'High', 'Medium', 'Low') 
  FROM DUAL;

The fake table DUAL is a special keyword. You can select from it, and it always returns exactly one row. But it has no real columns with data, so you can only select expressions.

Do this INSERT a few times and you get:

mysql> select * from mytable;                                                                                                                                       

+----+------------+-------------+----------+
| id | date       | description | priority |
+----+------------+-------------+----------+
|  1 | 2019-10-20 | NULL        | Medium   |
|  2 | 2020-05-17 | NULL        | High     |
|  3 | 2020-06-25 | NULL        | Low      |
|  4 | 2020-05-06 | NULL        | Medium   |
|  5 | 2019-09-30 | NULL        | High     |
|  6 | 2019-08-06 | NULL        | Low      |
|  7 | 2020-02-21 | NULL        | High     |
|  8 | 2019-11-10 | NULL        | High     |
|  9 | 2019-07-30 | NULL        | High     |
+----+------------+-------------+----------+

Here's a trick to use the number of rows in the table itself to insert the same number of rows, basically doubling the number of rows:

INSERT INTO mytable (date, priority)
  SELECT '2019-07-01' + INTERVAL FLOOR(RAND()*365) DAY, 
      ELT(1+FLOOR(RAND()*3), 'High', 'Medium', 'Low') 
  FROM mytable;

Just changing FROM DUAL to FROM mytable I change from selecting one row, to selecting the current number of rows from the table. But the values I insert are still random expressions, not the values already in those rows. So I get new rows with new random values.

Then repeat this INSERT as many times as you want to double the number of rows.