About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Thursday, September 13, 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: