CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
jrara
jrara

Reputation: 16981

SQL Server: how to insert random integers into table?

I have a test table like this

CREATE TABLE #temp (
    rid INT IDENTITY (1, 1) NOT NULL,
    val INT NULL,
    CONSTRAINT pk_temp_rid PRIMARY KEY (rid)
);

What are the different methods in SQL Server to insert random integers into this table (e.g for 1000 rows). While loop, cross join or what?

I tried this but the result is not correct

DECLARE @val AS INT = 1;

WHILE @val <= 1000
    BEGIN
        INSERT #temp (val)
        SELECT RAND(@val);
        SET @val = @val + 1;
    END

SELECT *
FROM   #temp;

Upvotes: 11

Views: 49339

Answers (4)

SPE109
SPE109

Reputation: 2951

This also done above

Insert Into @t
Select Cast(rand(checksum(newid()))*1000000  as int)
go 1000

See this link : https://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/

Upvotes: 10

NullRef
NullRef

Reputation: 3743

I think a `while is going to work. You are very close. 

DECLARE @val AS INT = 1;

WHILE @val <= 1000
BEGIN
    INSERT #temp (val)
    SELECT cast((RAND()*1000) as int);
    SET @val = @val + 1;
END

SELECT *
FROM   #temp;

Upvotes: 2

Yuck
Yuck

Reputation: 50855

From SQL SERVER – Random Number Generator Script:

SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (SELECT ABS(CAST(NEWID() AS binary(6)) % 1000) + 1 randomNumber
      FROM sysobjects) sample
GROUP BY randomNumber;

EDIT: Just to clarify, the script is grouping on the random number that was generated per row. So the total number of results is not guaranteed. You can be sure you'll never get more rows than SELECT COUNT(*) FROM sysobjects, though.

Upvotes: 7

Tamer
Tamer

Reputation: 84

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

Upvotes: 3

Related Questions