Does anyone have or know of a SQL script that will generate test data for a given table?
Ideally it will look at the schema of the table and create row(s) with test data based on the datatype for each column.
If this doesn't exist, would anyone else find it useful? If so I'll pull my finger out and write one.
Well I thought I would pull my finger out and write myself a light weight data generator:
declare @select varchar(max), @insert varchar(max), @column varchar(100),
@type varchar(100), @identity bit, @db nvarchar(100)
set @db = N'Orders'
set @select = 'select '
set @insert = 'insert into ' + @db + ' ('
declare crD cursor fast_forward for
select column_name, data_type,
COLUMNPROPERTY(
OBJECT_ID(
TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'IsIdentity') AS COLUMN_ID
from Northwind.INFORMATION_SCHEMA.COLUMNS
where table_name = @db
open crD
fetch crD into @column, @type, @identity
while @@fetch_status = 0
begin
if @identity = 0 or @identity is null
begin
set @insert = @insert + @column + ', '
set @select = @select +
case @type
when 'int' then '1'
when 'varchar' then '''test'''
when 'nvarchar' then '''test'''
when 'smalldatetime' then 'getdate()'
when 'bit' then '0'
else 'NULL'
end + ', '
end
fetch crD into @column, @type, @identity
end
set @select = left(@select, len(@select) - 1)
set @insert = left(@insert, len(@insert) - 1) + ')'
exec(@insert + @select)
close crD
deallocate crD
Given any table, the script will create one record with some arbitrary values for the types; int, varchar, nvarchar, smalldatetime and bit. The case statement could be replaced with a function. It won't travel down dependencies but it will skip any seeded columns.
My motivation for creating this is to test my NHibernate mapping files against a table with some 50 columns so I was after a quick a simple script which can be re-used.
Have you tried ApexSQL Generate: https://www.apexsql.com/sql_tools_generate.aspx ?
I stumbled upon it during my own search for the similar thing, and it did the job quite well. It’s not free, but you get a free trial with all features available, so you can try before you buy.
I think it will suite your needs quite well, since it keeps track of your relations between tables, column types and even constraints (for a more complex databases).
One thing I liked (and needed, actually) was that it has built-in values for actual names, addresses etc. It helps so much when querying created test data and not get a random strings.
Also, you can export to SQL (or few other formats) and use the created data at any time to repopulate the database.
There is a program from red gate software which will do this for you. It's called SQL Data Generator.
We need step by step create query for tables need entry data. i used below codes, step by step for insert test data:
1. Create a table :
CREATE TABLE dbo.TestTableSize
(
MyKeyField VARCHAR(10) NOT NULL,
MyDate1 DATETIME NOT NULL,
MyDate2 DATETIME NOT NULL,
MyDate3 DATETIME NOT NULL,
MyDate4 DATETIME NOT NULL,
MyDate5 DATETIME NOT NULL
)
2. Variable Declarations
DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME
3.Set on time :
SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0
4.Populate the Table :
WHILE @RowCount < 3000000
BEGIN
5.Preparing Values
SET @RowString = CAST(@RowCount AS VARCHAR(10))
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SET @InsertDate = DATEADD(dd, @Random, GETDATE())
6. Write insert statment :
INSERT INTO TestTableSize
(MyKeyField
,MyDate1
,MyDate2
,MyDate3
,MyDate4
,MyDate5)
VALUES
(REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
, @InsertDate
,DATEADD(dd, 1, @InsertDate)
,DATEADD(dd, 2, @InsertDate)
,DATEADD(dd, 3, @InsertDate)
,DATEADD(dd, 4, @InsertDate))
SET @RowCount = @RowCount + 1
END
7. Complete code :
DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME
SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0
WHILE @RowCount < 3000000
BEGIN
SET @RowString = CAST(@RowCount AS VARCHAR(10))
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SET @InsertDate = DATEADD(dd, @Random, GETDATE())
INSERT INTO TestTableSize
(MyKeyField
,MyDate1
,MyDate2
,MyDate3
,MyDate4
,MyDate5)
VALUES
(REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
, @InsertDate
,DATEADD(dd, 1, @InsertDate)
,DATEADD(dd, 2, @InsertDate)
,DATEADD(dd, 3, @InsertDate)
,DATEADD(dd, 4, @InsertDate))
SET @RowCount = @RowCount + 1
END
Some flavours of Visual Studio have data generation built in. If you use database projects in it you can create data generation plans. Here's the MSDN article
I used following way it basically copies data from itself , the data grows exponentially with every execution.Claveat is that You have to have some sample data at first and also you have to execute the query eg I had 327680 rows of data when i started with 10 rows of data .by executing the query just 16 times.Execute one more time and i will hage 655360 rows of data!
insert into mytable select [col1], [col2], [col3] from mytable
