Fill SQL Server table with test data

Question

Is there a free utility that will fill up your database tables with test data?

A little more complicated that it seems. For example, if you are doing American addresses, should city, state and zip be legitimate or just random values. Should state be just the valid two letter abbreviations? I have SQL script that I can send you to generate random people names if that would help. — Sparky, Oct 27 '11 at 22:10
I use my own c# utility to generate data using regexp. This way I get to test DAL as well. The utility is available at http://code.google.com/p/rxrdg/ — Goran, Oct 28 '11 at 06:25

score 7 · Answer 1 · answered Oct 27 '11 at 22:30

7

If you develop with VS2010 Premium or Ultimate, it has built-in data generator. You need to create SQL Server 2008 Database Project, add "Data Generation Plan" to it. This article has some information how to use it.

answered Oct 27 '11 at 22:30

Alexey

909
6
11

score 6 · Answer 2 · answered Oct 27 '11 at 21:49

I don't believe there's a free one out there, but if you are willing to part with some money RedGate offers SQL Data Generator.

Or you could always write your own. That way you know what data is going in there.

Here is a Fake Name Generator. I don't believe it does datasets, but if you are looking to create fake client data or that sort of thing, you could spend some time there and get some "legitimate" looking data.

score 3 · Accepted Answer · edited Mar 26 '12 at 13:13

Check this:

--Declare variables

DECLARE @NoOfRows INT, @StartVal INT, @EndVal INT, @Range INT

--Preset the variables

 SELECT @NoOfRows = 10000, @StartVal = 10, @EndVal = 20, @Range = @EndVal - @StartVal + 1

--Create the test table with "random values" integers and floats

SELECT TOP (@NoOfRows) 
SomeRandomInteger =  ABS(CHECKSUM(NEWID())) % @Range + @StartVal, 
SomeRandomFloat = RAND(CHECKSUM(NEWID())) * @Range + @StartVal

INTO #TempTable

FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

SELECT * FROM #TempTable

Fill SQL Server table with test data

3 Answers3