13 September 2007

Preparing test data

    There are cases in which we need to create test data in order to check a certain functionality. In this post I'll show how can be created data with the help of loops. For this let's create a simple table containing 5 fields: CREATE TABLE Table1( ID int NOT NULL , [Year] int NOT NULL , Product varchar(30) NOT NULL , Price decimal(13,2) NOT NULL , Quantity decimal(13,3) NOT NULL) --DROP TABLE Table1 --TRUNCATE TABLE Table1     Usually I'm commenting DROP and TRUNCATES so I can avoid the modification of data by mistake.     And now the loop, with the help of RAND and modulos the values can be varied. DECLARE @index int SET @index = 1 WHILE @index < color="#000099">BEGIN INSERT Table1(ID, [Year], Product, Price, Quantity) VALUES (@index, 2000 + (@index % 4), 'Product ' + Cast(@index%10 as char(3)), 10 * RAND(), 100 * RAND()) SET @index = @index + 1 END --testing the output SELECT * FROM Table1     And now you can work with the data and write nice statements like the below one: SELECT [Year], Product, SUM(Price * Quantity) Amount FROM Table1 GROUP BY [Year], Product ORDER BY [Year], Product That's all folks!

No comments: