Quick way to insert test data into a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Quick way to insert test data into a table

Author

Topic

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-13 : 04:48:51

Does anyone know how you can build a table of data quickly where you declare the number of rows and then declare a max and min of values you want to assign.

For example: Iwant to create a table with 100 rows and randomly assign an INT value of between 1 and 10.

Many Thanks for any advice

Paul

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-13 : 09:36:00

I'm pretty certain that someone will weigh in with a more efficient insert. I know that using the RAND function may be a bit more reliable, but this works to insert 1 thru 10 up to 100 (so 1-10, 10 times). The ordering is random based on the NEWID() in the group by clause.

[CODE]
declare @test table (Number int, ID uniqueidentifier );

with mycte (Number, ID)
as (Select number, NEWID()
From master..spt_values
Where type='p' and number between 1 and 10)

insert into @test (Number, ID )
(select A.Number, A.ID
From Mycte A
Cross Join mycte B
group by A.ID, A.Number)

select Number
from @test
[/CODE]

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 10:12:35

"I know that using the RAND function may be a bit more reliable"

Pretty sure that if you use RAND you will get the same value on every row (unless you use a loop / cursor, which will be slower than set-based)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-13 : 15:49:24

@Kristen :) Just second guessing myself

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-09-13 : 19:48:03

[CODE]declare
@MinValue int = 10,

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-09-13 : 19:48:04

[CODE]declare
@MinValue int = 10,
@MaxValue int = 50

insert into MyTable(MyValue, AndOtherColumns)
select @MinValue + (@MaxValue - @MinValue) * RAND(), 'OtherColumnValues'
go 100[/CODE]This will make 100 entries in the range but doesn't guarantee that the Min/Max values will appear; just that the range is constrained. You could explicitly add two entries, if desired.

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-14 : 09:58:51

Thanks for everyones advice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources