Hi All
I want to blow up the database. Say I have 10 records in a particular table
and I want to increase it to 100 records for performance testing purposes. I
s
there a way to do it in SQL Server?
Thank you
Viccu.Viccu
create table #test (id int not null identity(1,1))
declare @.i int
set @.i=1
while @.i<=100
begin
insert #test default values
set @.i=@.i+1
end
select * from #test
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||How about that. In answering your question I grabbed a function that was
posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
microsoft.public.sqlserver.programming there are 12 posts between yours and
his).
Anyway...
--Lets pretend this is your table:
create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime)
go
--and here is your 10 rows of data
insert into #foo (col2, col3) values ('test 1', '1/1/2006')
insert into #foo (col2, col3) values ('test 2', '2/1/2006')
insert into #foo (col2, col3) values ('test 3', '1/3/2006')
insert into #foo (col2, col3) values ('test 4', '1/4/2006')
insert into #foo (col2, col3) values ('test 5', '5/1/2006')
insert into #foo (col2, col3) values ('test 6', '1/6/2006')
insert into #foo (col2, col3) values ('test 7', '1/7/2006')
insert into #foo (col2, col3) values ('test 8', '8/1/2006')
insert into #foo (col2, col3) values ('test 9', '1/9/2006')
insert into #foo (col2, col3) values ('test 10', '10/1/2006')
--Peter Larson's function:
CREATE FUNCTION dbo.fnSeqNumbers
(
@.LowLimit INT,
@.HighLimit INT
)
RETURNS @.Values TABLE
(
Value INT
)
AS
BEGIN
DECLARE @.Temp INT
IF @.LowLimit > @.HighLimit
SELECT @.Temp = @.LowLimit,
@.LowLimit = @.HighLimit,
@.HighLimit = @.Temp
INSERT @.Values VALUES (@.LowLimit)
WHILE @.@.ROWCOUNT > 0
INSERT @.Values
SELECT n.Value + t.Items
FROM @.Values n
CROSS JOIN (
SELECT COUNT(*) Items
FROM @.Values
) t
WHERE n.Value + t.Items <= @.HighLimit
RETURN
END
go
-- showing you the results from the function:
--select * from dbo.fnSeqNumbers(1,10)
--this returns 100 rows
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,10) b
order by a.col2
--...but you already have 10 in your table so lets only insert 90 rows
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
select @.@.rowcount
--look at your "new" data:
select * From #foo
This solution assumes that you have an identity value as your primary key.
If that is not the case in your table but your primary key is a number you
should be able to convert the number to a string, concatinate the value from
the function, and convert it back to an int that could be used as your
primary key. Hopefully this example gives you a few ideas and points you in
a direction where you can figure out a solution.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
> Hi All
> I want to blow up the database. Say I have 10 records in a particular
> table
> and I want to increase it to 100 records for performance testing purposes.
> Is
> there a way to do it in SQL Server?
> Thank you
> Viccu.|||Thanks much.
"Uri Dimant" wrote:
> Viccu
> create table #test (id int not null identity(1,1))
> declare @.i int
> set @.i=1
> while @.i<=100
> begin
> insert #test default values
> set @.i=@.i+1
> end
> select * from #test
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||Thank you so much.
"Keith Kratochvil" wrote:
> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||google up "generate test data"|||Thank you.Because of the foreign key constraints,say one of my tables has a
value that should
correspond to the value in the other table. How would I include that
particular value
in the query given by you?
insert into #foo (col2, col3)
select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
cross join dbo.fnSeqNumbers(1,9) b
order by a.col2
"Keith Kratochvil" wrote:
> How about that. In answering your question I grabbed a function that was
> posted to this newsgroup by Peter Larson on 5/10/2006 @. 4:13AM (within
> microsoft.public.sqlserver.programming there are 12 posts between yours an
d
> his).
> Anyway...
> --Lets pretend this is your table:
> create table #foo (col1 int identity(1,1), col2 varchar(10), col3 datetime
)
> go
> --and here is your 10 rows of data
> insert into #foo (col2, col3) values ('test 1', '1/1/2006')
> insert into #foo (col2, col3) values ('test 2', '2/1/2006')
> insert into #foo (col2, col3) values ('test 3', '1/3/2006')
> insert into #foo (col2, col3) values ('test 4', '1/4/2006')
> insert into #foo (col2, col3) values ('test 5', '5/1/2006')
> insert into #foo (col2, col3) values ('test 6', '1/6/2006')
> insert into #foo (col2, col3) values ('test 7', '1/7/2006')
> insert into #foo (col2, col3) values ('test 8', '8/1/2006')
> insert into #foo (col2, col3) values ('test 9', '1/9/2006')
> insert into #foo (col2, col3) values ('test 10', '10/1/2006')
>
> --Peter Larson's function:
> CREATE FUNCTION dbo.fnSeqNumbers
> (
> @.LowLimit INT,
> @.HighLimit INT
> )
> RETURNS @.Values TABLE
> (
> Value INT
> )
> AS
> BEGIN
> DECLARE @.Temp INT
> IF @.LowLimit > @.HighLimit
> SELECT @.Temp = @.LowLimit,
> @.LowLimit = @.HighLimit,
> @.HighLimit = @.Temp
> INSERT @.Values VALUES (@.LowLimit)
> WHILE @.@.ROWCOUNT > 0
> INSERT @.Values
> SELECT n.Value + t.Items
> FROM @.Values n
> CROSS JOIN (
> SELECT COUNT(*) Items
> FROM @.Values
> ) t
> WHERE n.Value + t.Items <= @.HighLimit
> RETURN
> END
> go
> -- showing you the results from the function:
> --select * from dbo.fnSeqNumbers(1,10)
>
> --this returns 100 rows
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,10) b
> order by a.col2
>
> --...but you already have 10 in your table so lets only insert 90 rows
>
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
> select @.@.rowcount
>
> --look at your "new" data:
>
> select * From #foo
>
> This solution assumes that you have an identity value as your primary key.
> If that is not the case in your table but your primary key is a number you
> should be able to convert the number to a string, concatinate the value fr
om
> the function, and convert it back to an int that could be used as your
> primary key. Hopefully this example gives you a few ideas and points you
in
> a direction where you can figure out a solution.
> --
> Keith Kratochvil
>
> "Viccu" <Viccu@.discussions.microsoft.com> wrote in message
> news:0324B0FE-8D59-4755-A736-4FCA8D844EF3@.microsoft.com...
>
>|||You could hardcode a single value within the select, modify the function to
return an additional column that has "random" FK values passed out of it or
you could just use (re-use) the foreign key data that you already have in
your table (#foo in my example). You probably want to load all the columns
when you insert data...just make sure that you query your FK column from
your base table when joining to the function.
Keith Kratochvil
"Viccu" <Viccu@.discussions.microsoft.com> wrote in message
news:D11F6AF9-11D2-43FB-AF0D-40726220E012@.microsoft.com...
> Thank you.Because of the foreign key constraints,say one of my tables has
> a
> value that should
> correspond to the value in the other table. How would I include that
> particular value
> in the query given by you?
> insert into #foo (col2, col3)
> select a.col2 + ' ' + LTRIM(STR(b.Value)), a.col3 From #foo a
> cross join dbo.fnSeqNumbers(1,9) b
> order by a.col2
sql
There are must be a better way to do it.
Mikesdotnetting:
, I finally found something that seems to