-- Assumes the existence of a table called BIG_SALES, a copy of pubs..sales
SET NOCOUNT ON
DECLARE @separator VARCHAR(25),
@message VARCHAR(25),
@counter INT,
@ord_nbr VARCHAR(20),
@order_date DATETIME,
@store_nbr INT,
@qty_sold INT,
@terms VARCHAR(12),
@title CHAR(6),
@starttime DATETIME
SET @STARTTIME = GETDATE()
SELECT @counter = 0,
@separator = REPLICATE( '-', 25 )
WHILE @counter < 9999
BEGIN
SET @counter = @counter + 1
SET @ord_nbr = 'Y' + CAST(@counter AS VARCHAR(5))
SET @order_date = DATEADD(hour, (@counter * 8), 'Jan 01 1999')
SET @store_nbr =
CASE WHEN @counter < 999 THEN '6380'
WHEN @counter BETWEEN 1000 AND 2999 THEN '7066'
WHEN @counter BETWEEN 3000 AND 3999 THEN '7067'
WHEN @counter BETWEEN 4000 AND 6999 THEN '7131'
WHEN @counter BETWEEN 7000 AND 7999 THEN '7896'
WHEN @counter BETWEEN 8000 AND 9999 THEN '8042'
ELSE '6380'
END
SET @qty_sold =
CASE WHEN @counter BETWEEN 0 AND 2999 THEN 11
WHEN @counter BETWEEN 3000 AND 5999 THEN 23
ELSE 37
END
SET @terms =
CASE WHEN @counter BETWEEN 0 AND 2999 THEN 'Net 30'
WHEN @counter BETWEEN 3000 AND 5999 THEN 'Net 60'
ELSE 'On Invoice'
END
-- SET @title = (SELECT title_id FROM big_sales WHERE qty = (SELECT MAX(qty)
FROM big_sales))
SET @title =
CASE WHEN @counter < 999 THEN 'MC2222'
WHEN @counter BETWEEN 1000 AND 1999 THEN 'MC2222'
WHEN @counter BETWEEN 2000 AND 3999 THEN 'MC3026'
WHEN @counter BETWEEN 4000 AND 5999 THEN 'PS2106'
WHEN @counter BETWEEN 6000 AND 6999 THEN 'PS7777'
WHEN @counter BETWEEN 7000 AND 7999 THEN 'TC3218'
ELSE 'PS1372'
END
-- PRINT @separator
-- SELECT @message = STR( @counter, 10 ) -- + STR( SQRT( CONVERT( FLOAT,
@counter ) ), 10, 4 )
-- PRINT @message
BEGIN TRAN
INSERT INTO [pubs].[dbo].[big_sales]([stor_id], [ord_num], [ord_date],
[qty], [payterms], [title_id])
VALUES(@store_nbr, CAST(@ord_nbr AS CHAR(5)), @order_date, @qty_sold,
@terms, @title)
COMMIT TRAN
END
SET @message = CAST(DATEDIFF(ms, @starttime, GETDATE()) AS VARCHAR(20))
PRINT @message
/*
TRUNCATE table big_sales
INSERT INTO big_sales
SELECT * FROM sales
SELECT title_id, sum(qty)
FROM big_sales
group by title_id
order by sum(qty)
SELECT * FROM big_sales
*/
| 
