TSQL -> Insert random number to id column using Rand() function

Suppose that we have id column in some table and want to fill it with a random numbers. It can be done using Rand() function. A number that function returns is bigger/equal to zero and less than 1.

/* create temp table */

CREATE TABLE #temp_tb
(
idno INT NOT NULL PRIMARY KEY,
curr_dt DateTime NULL
);
GO

/*
"SELECT Rand()" Will return result like: 0.746842095495527. In order to recive random number in the range between 1 and 1000 we can use formula:
ROUND(((1000 - 1 + 1) * Rand()+1),0)
*/

INSERT INTO #temp_tb (idno, curr_dt)
SELECT ROUND(((1000 - 1 + 1) * Rand()+1),0), getdate()
  UNION ALL
SELECT ROUND(((1000 - 1 + 1) * Rand()+1),0), getdate()
  UNION ALL
SELECT ROUND(((1000 - 1 + 1) * Rand()+1),0), getdate();
GO

/* now select data from temp table */

SELECT * FROM #temp_tb ORDER BY idno;
GO

DROP TABLE #temp_tb;



Query results will came as following:
idnocurr_dt
1692008-05-25 12:55:59.020
6032008-05-25 12:55:56.240
8012008-05-25 12:55:53.270

/*
alternatively we can set default property of id column using formula based on Rand() function
*/

CREATE TABLE #temp_tb
(
idno INT NULL DEFAULT ROUND(((1000 - 1 + 1) * Rand()+1),0),
curr_dt DateTime NULL
);
GO

-- now we'll made 3 inserts one after another

INSERT INTO #temp_tb (curr_dt) SELECT getdate();
INSERT INTO #temp_tb (curr_dt) SELECT getdate();
INSERT INTO #temp_tb (curr_dt) SELECT getdate();
GO

/* select data from temp table */

SELECT * FROM #temp_tb ORDER BY idno;
GO


Query results will came as following:
idnocurr_dt
1852008-05-25 14:17:51.393
1922008-05-25 14:17:51.396
6132008-05-25 14:17:51.398


sqlexamples.info