mytest_order_fill.sql

/*==============================================
*                                              *
*         fill in table orders and items       *
*                                              *
==============================================*/


USE mytest;

TRUNCATE TABLE orders;
TRUNCATE TABLE items;


-- =========== fill in table orders and items =============

DECLARE @number_of_orders_to_create INT = 200000 -- number of orders to insert in table orders and items
DECLARE @max_item_count             INT = 5      -- max number of items per order


DECLARE @i INT = 0

DECLARE @mincustid INT, @maxcustid INT, @deltacustid INT

DECLARE @custid         INT
DECLARE @order_date     DATE
DECLARE @shipping_date  DATE
DECLARE @street         VARCHAR(20)
DECLARE @city           VARCHAR(20)
DECLARE @country_id     INT
DECLARE @country        VARCHAR(20)
DECLARE @total          NUMERIC(12, 2)

DECLARE @minprodid INT, @maxprodid INT, @deltaprodid INT

DECLARE @item_count     INT

DECLARE @k             INT
DECLARE @oid           INT
DECLARE @prodid        INT
DECLARE @price         NUMERIC(12, 2)
DECLARE @quantity      INT
DECLARE @itemprice     NUMERIC(12, 2)

SELECT @mincustid = MIN(custid), @maxcustid = MAX(custid)
FROM customer

SET @deltacustid = @maxcustid - @mincustid


SELECT @minprodid = MIN(prodid), @maxprodid = MAX(prodid)
FROM product

SET @deltaprodid = @maxprodid - @minprodid


PRINT 'Start inserting ' + CAST(@number_of_orders_to_create as VARCHAR(100)) + ' orders, with at most ' + CAST(@max_item_count as VARCHAR(100)) + ' items per order. ' + CAST(GETDATE() as VARCHAR(100))


SET NOCOUNT ON
BEGIN TRAN

WHILE @i < @number_of_orders_to_create
    BEGIN
    SET @custid        = NULL
    SET @order_date    = NULL
    SET @shipping_date = NULL
    SET @street        = NULL
    SET @city          = NULL
    SET @country_id    = NULL
    SET @country       = NULL

    PICKUP_RANDOM_CUSTUMER:
    SET @custid = @mincustid + CAST(RAND()*(@deltacustid + 1) as INT) -- pick up a random customer

    -- lookup customer info

    SELECT @street = street, @city = city, @country_id = country_id
    FROM customer
    WHERE custid = @custid

    IF @@ROWCOUNT = 0 -- if custid not found, try with another one
        GOTO PICKUP_RANDOM_CUSTUMER

    IF @street IS NULL
        SET @street = 'Nowhere Steet'

    IF @city IS NULL
        SET @city = 'Shambala'

    -- lookup country name

    IF @country_id IS NOT NULL
        BEGIN

        SELECT @country = name
        FROM country
        WHERE cid = @country_id

        END

    IF @country IS NULL
        SET @country = 'NORTH POLE'

    -- create random order and shipping date

    SET @order_date = DATEADD(day, RAND()*365*6, CAST('20100101' as DATE))

    SET @shipping_date = DATEADD(day, CAST(RAND()*8 as INT), @order_date)

    -- @total

    SET @total = 0

    -- insert the order

    INSERT INTO orders (custid, order_date, shipping_date, to_street, to_city, to_country, total) VALUES(@custid, @order_date, @shipping_date, @street, @city, @country, @total)
    SET @oid = SCOPE_IDENTITY()

    -- insert order items

    SET @item_count = CAST(RAND()*@max_item_count+1 as INT)

    SET @price = 0
    SET @k = 0
    WHILE @k < @item_count
        BEGIN
        SET @prodid = @minprodid + CAST(RAND()*(@deltaprodid+1) as INT)
        SET @price = NULL

        SELECT @price = price
        FROM product
        WHERE prodid = @prodid

        SET @quantity = CAST(RAND() * 20 as INT)
        SET @itemprice = CAST(@quantity * @price as NUMERIC(12,2))

        INSERT INTO items (oid, line_no, prodid, quantity, price) VALUES (@oid, @k, @prodid, @quantity, @itemprice)

        SET @total += ISNULL(@itemprice, 0)

        SET @k += 1
        END

    -- update order total

    UPDATE orders
    SET total = @total
    WHERE oid = @oid

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

COMMIT
SET NOCOUNT OFF

-- a little sample query

PRINT ''
PRINT 'Some little sample queries'

select count(*), sum(total) as [orders total] from orders

select count(*), sum(price) [items total] from items