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