mytest_customer_fill.sql
/*==============================================
* *
* fill in table customer *
* *
==============================================*/
-- the variable @number_of_customers_to_create specifies the number of customers you want to insert into the table
USE mytest;
TRUNCATE TABLE customer;
DECLARE @number_of_customers_to_create INT = 100000 -- number of customers to insert in customer table
DECLARE @consonant VARCHAR(100) = 'bcdfghjklmnprstv'
DECLARE @vowel VARCHAR(100) = 'aeioue'
DECLARE @choice INT = 0-- 0 consonant, 1 vowel
DECLARE @i INT = 0
DECLARE @k INT
DECLARE @id_min_value INT
DECLARE @len INT
DECLARE @firstname VARCHAR(100)
DECLARE @minlen INT = 4, @maxlen INT = 15
DECLARE @lastname VARCHAR(100)
DECLARE @birthdate DATE
DECLARE @gender_val FLOAT
DECLARE @gender VARCHAR(100)
DECLARE @street VARCHAR(100)
DECLARE @minstreetlen INT = 4, @maxstreetlen INT = 10
DECLARE @city VARCHAR(100)
DECLARE @mincitylen INT = 4, @maxcitylen INT = 10
DECLARE @country_count INT
DECLARE @country_id INT
PRINT 'Start inserting ' + CAST(@number_of_customers_to_create as VARCHAR(100)) + ' records. ' + CAST(GETDATE() as VARCHAR(100))
SET NOCOUNT ON
BEGIN TRAN
SET @country_count = NULL -- not useful here, but it is good practice to set variables used in SELECT to NULL, because their values are not changed when SELECT returns no row
SELECT @country_count = COUNT(*) FROM country
WHILE @i < @number_of_customers_to_create
BEGIN
-- create random first name
SET @len = @minlen + CAST(RAND()*(@maxlen + 1 - @minlen) as INT) -- number between @minlen and @maxlen inclusive
SET @k = 0
SET @firstname = ''
WHILE @k < @len
BEGIN
IF @choice = 0 or RAND() < CAST(0.1 as FLOAT) -- favour consonnant a little bit
BEGIN
SET @firstname += SUBSTRING(@consonant, 1+CAST(RAND()*LEN(@consonant) as INT), 1)
SET @choice = 1
END
ELSE
BEGIN
SET @firstname += SUBSTRING(@vowel, 1+CAST(RAND()*LEN(@vowel) as INT), 1)
SET @choice = 0
END
SET @k += 1
END
SET @firstname = UPPER(SUBSTRING(@firstname, 1, 1)) + SUBSTRING(@firstname, 2, LEN(@firstname) - 1)
-- create random last name
SET @len = @minlen + CAST(RAND()*(@maxlen + 1 - @minlen) as INT) -- number between @minlen and @maxlen inclusive
SET @k = 0
SET @lastname = ''
WHILE @k < @len
BEGIN
IF @choice = 0 or RAND() < CAST(0.1 as FLOAT) -- favour consonnant a little bit
BEGIN
SET @lastname += SUBSTRING(@consonant, 1+CAST(RAND()*LEN(@consonant) as INT), 1)
SET @choice = 1
END
ELSE
BEGIN
SET @lastname += SUBSTRING(@vowel, 1+CAST(RAND()*LEN(@vowel) as INT), 1)
SET @choice = 0
END
SET @k += 1
END
SET @lastname = UPPER(@lastname)
-- create random birthdate
SET @birthdate = DATEADD(day, RAND()*365*116, CAST('19000101' as DATE))
-- create random gender
SET @gender_val = RAND()
SET @gender = CASE WHEN @gender_val < CAST(0.42 as FLOAT) THEN 'M'
WHEN @gender_val < CAST(0.84 as FLOAT) THEN 'F'
WHEN @gender_val < CAST(0.9 as FLOAT) THEN 'X'
ELSE NULL
END
-- create random street
SET @len = @minstreetlen + CAST(RAND()*(@maxstreetlen + 1 - @minstreetlen) as INT) -- number between @minstreetlen and @maxstreetlen inclusive
SET @k = 0
SET @street = ''
WHILE @k < @len
BEGIN
IF @choice = 0 or RAND() < CAST(0.1 as FLOAT) -- favour consonnant a little bit
BEGIN
SET @street += SUBSTRING(@consonant, 1+CAST(RAND()*LEN(@consonant) as INT), 1)
SET @choice = 1
END
ELSE
BEGIN
SET @street += SUBSTRING(@vowel, 1+CAST(RAND()*LEN(@vowel) as INT), 1)
SET @choice = 0
END
SET @k += 1
END
SET @street = UPPER(SUBSTRING(@street, 1, 1)) + SUBSTRING(@street, 2, LEN(@street) - 1)
SET @street = CONCAT(CAST(CAST(RAND()*50 as INT) as VARCHAR(20)), ' ', @street, ' St.')
-- create random city
SET @len = @mincitylen + CAST(RAND()*(@maxcitylen + 1 - @mincitylen) as INT) -- number between @mincitylen and @maxcitylen inclusive
SET @k = 0
SET @city = ''
WHILE @k < @len
BEGIN
IF @choice = 0 or RAND() < CAST(0.1 as FLOAT) -- favour consonnant a little bit
BEGIN
SET @city += SUBSTRING(@consonant, 1+CAST(RAND()*LEN(@consonant) as INT), 1)
SET @choice = 1
END
ELSE
BEGIN
SET @city += SUBSTRING(@vowel, 1+CAST(RAND()*LEN(@vowel) as INT), 1)
SET @choice = 0
END
SET @k += 1
END
SET @city = CONCAT(UPPER(@city), ' ', CAST(RAND()*8999 as INT))
-- create random country
SET @country_id = CAST(RAND() * @country_count as INT)
-- insert into customer table
INSERT INTO customer(firstname, lastname, birthdate, gender, street, city, country_id) VALUES (@firstname, @lastname, @birthdate, @gender, @street, @city, @country_id)
IF @id_min_value IS NULL
SET @id_min_value = SCOPE_IDENTITY()
-- print inserted record count
SET @i += 1
IF @i % 10000 = 0
PRINT 'Inserted records: ' + CAST(@i as VARCHAR(20)) + ' ' + CAST(GETDATE() as VARCHAR(100))
END
-- update some clients with ordinary names
UPDATE customer
SET firstname = 'Hayley', lastname = 'WESTENRA', birthdate = '19870410', gender = 'F', country_id = 8
WHERE custid = CAST(@id_min_value + CAST(@number_of_customers_to_create as FLOAT) / 1000 as INT)
UPDATE customer
SET firstname = 'Albert', lastname = 'EINSTEIN', birthdate = '18790314', gender = 'M', country_id = 0
WHERE custid = CAST(@id_min_value + CAST(@number_of_customers_to_create as FLOAT) / 4 as INT)
UPDATE customer
SET firstname = 'Charles', lastname = 'TRENET', birthdate = '19130518', gender = 'M', country_id = 1
WHERE custid = CAST(@id_min_value + CAST(@number_of_customers_to_create as FLOAT) / 3 as INT)
UPDATE customer
SET firstname = 'Vivien', lastname = 'LEIGH', birthdate = '19131105', gender = 'F', country_id = 9
WHERE custid = CAST(@id_min_value + CAST(@number_of_customers_to_create as FLOAT) / 2 as INT)
UPDATE customer
SET firstname = 'Captain', lastname = 'HARLOCK', birthdate = '18000101', gender = 'M', country_id = 5
WHERE custid = CAST(@id_min_value + CAST(@number_of_customers_to_create as FLOAT) / 1.2 as INT)
UPDATE customer
SET firstname = 'John', lastname = 'DOE', birthdate = '19501001', gender = 'M', country_id = 0
WHERE custid = CAST(@id_min_value + CAST(@number_of_customers_to_create as FLOAT) / 1.001 - 1 as INT)
-- COMMIT the changes
PRINT 'COMMIT all changes. ' + CAST(GETDATE() as VARCHAR(100))
COMMIT
SET NOCOUNT OFF
PRINT 'COMMIT is finished. ' + CAST(GETDATE() as VARCHAR(100))
-- a little sample query
PRINT ''
PRINT 'Some little sample queries'
SELECT cn.name country, gender, count(*) count FROM customer c, country cn
where cn.cid = c.country_id
group by cn.name, gender
ORDER BY cn.name, gender
SELECT * FROM customer
WHERE lastname = 'DOE'