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'


RSQL, a simple alternative to Microsoft SQL Server