Creating Test Data for a Table

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

u can use some existing table...transform and import that data...

or..there are a few data generator tools u can search and use them.... if u have visual studio 2005 for database developer, that has a cool data generation functionality too...

|||

Thanx, though I was looking for a T-SQL query equivalent solution

Maybe a while loop? I'm not so good at writing code in sql, any other suggestions please?

|||

JohDas wrote:

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

INSERT INTO
MyTable (id, Description)
(
SELECT
Id, Description
FROM
MyOtherTable
)|||

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

|||

ManiD wrote:

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

Nice, after seeing this I realised I mis understood the question |||Thanx a lot, this is the query I've been searching!!!|||

JohDas:

I try to avoid these WHILE loops as much as possible. I normally try to use a "numbers" table to generate my test data; this is normally much more efficient. One of the tools that I find helpful for generating repetitive data is the modulo "%" operator

-- --
-- Give a look to this link for a description of the use of a
-- "numbers" table:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
--
-- A table that can be used on a "test only" basis is the
-- "master.dbo.spt_values" table; but be forwarned: this type
-- of use is not supported! In fact, if you use this table with
-- SQL Server 2000 you will find that you get a different range
-- of values for the "number" column than you do with SQL Server
-- 2005.
--
-- Nonetheless, I still frequently use this table as a source of
-- values for test data. If the range of values from this table
-- is not sufficient to provide enough data, then I might
-- generate my data by cross joining the spt_values table with
-- itself.
-- --
select number,
'Description #' + convert (varchar(10), number%21) Description
from master.dbo.spt_values (nolock)
where name is null

-- Output:

-- number Description
-- -- --
-- 0 Description #0
-- 1 Description #1
-- 2 Description #2
-- ...
-- 20 Description #20
-- 21 Description #0
-- 22 Description #1
-- ...

|||

Thanx Mugambo, it's a more complex solution (to comprehend ;) ) but I think it'll be faster