Sample Data Generation Script

sample-data-gen.sql

-- DB to use
use Puzzles;

-- Remove the Table and the SP if it already exists
drop table dbo.generated_table;
drop procedure dbo.addRows;

-- Define the Table to be populated with generated data
CREATE TABLE dbo.generated_table (
	id int --PRIMARY KEY
	,number int
	,name nvarchar(10)
	,dt datetime
);

-- Drop addRows procedure if it already exists
IF OBJECT_ID ('dbo.addRows', 'P') IS NOT NULL
	DROP PROCEDURE dbo.addRows;
GO

-- Create addRows procedure to generate rows
CREATE PROCEDURE dbo.addRows 
	@rowsNumber int
AS
BEGIN
	SET NOCOUNT ON

	-- start point for adding rows
	DECLARE @id INT = ISNULL((SELECT MAX(id) FROM dbo.generated_table)+1, 1)
	DECLARE @iteration INT = 0
	WHILE @iteration < @rowsNumber
		BEGIN
			--get a random int from 0 to 1000
			DECLARE @number INT = CAST(RAND()*1000 AS INT)
			DECLARE @dt datetime 
			-- SELECT @dt = GETDATE()
			SET @dt = '2000-01-01 00:00:00'


			-- generate random nvarchar
			-- get a random nvarchar ascii char 65 to 128
			DECLARE @name NVARCHAR(10) = N'' --empty string for start
			DECLARE @length INT = CAST(RAND() * 10 AS INT) --random length of nvarchar
			SET @dt = dateadd(HOUR, @number, @dt)
			SET @dt = dateadd(SECOND, @number, @dt)
			WHILE @length <> 0 --in loop we will randomize chars till the last one
				BEGIN
					SELECT @name = @name + CHAR(CAST(RAND() * 10 + 65 as INT))
					SET @length = @length - 1 --next iteration
				END

			--insert data
			INSERT INTO dbo.generated_table (id, number, name, dt)
			VALUES (@id, @number, @name, @dt)
			SET @iteration += 1
			SET @id += 1
		END
	SET NOCOUNT OFF
END
GO

-- Add the requested number of random rows
EXEC dbo.addRows 10000
select max(id), max(number) from dbo.generated_table;
select TOP 10 * from dbo.generated_table;