load sample data in sql server table for testing

How to add fake or sample data in sql server database, if you want to load data in sql server table to run any kind of testing on millions or rows and wondering where to get that much data then here is a script. I wrote a basic script for a friend of mine to add millions of rows in single table to do any kind of performance testing he need.

The script will run on SQL Server 2005, you can follow the logic to add some more tables if you need.

--Create a table
CREATE TABLE #tmpOrders
(
 OrderID int,
 OrderDate datetime,
 CustomerID varchar(15),
 ProductID int,
 Quantity int,
 OrderTotal money,
 Shipped bit
)
-- Declare variables
Declare @cntr int
Declare @qty smallint
Declare @prod smallint
Declare @rnddate smallint
Declare @ship smallint

SET @cntr = 1

-- Change the following line to add more rows
-- It will add 99 rows
While @cntr < 100
BEGIN

SET @qty = RAND() * 100 + 1
SET @prod = RAND() * 25 + 1
SET @rnddate = RAND() * 1825
SET @ship = RAND() * 2

INSERT INTO #tmpOrders VALUES(
@cntr,
getdate() - @rnddate,
'Customer' + CAST(@qty as char),
@prod,
@qty,
@qty * 9.95,
@ship)
SET @cntr = @cntr + 1
END

-- Uncomment the following line to view rows
--SELECT * FROM #tmpOrders

-- Uncomment the next line to drop the table
--DROP TABLE #tmpOrders

 



 6/5/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.