repeating an insert statement

I want a insert statement to repeat X number of times (X will be a variable
passed from a user interface)...any hints on how to code the sql'
Thanks!!!Put it in a stored proc with a WHILE Loop.
CREATE PROC YourProc
@.Loop INT
AS
WHILE @.Loop > 0
BEGIN
INSERT INTO Table VALUES (x)
SET @.Loop = @.Loop - 1
END
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||And I might add that if X happens to be a large number, there may be a
need--for better performance--to control the number of INSERTs you want to
commit in a single transaction. By default, each INSERT commits as a single
transaction, which may not be most efficient if you are doing many
single-INSERT commits in a row.
Linchi
"Gerry M" wrote:
> I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!
What data do you want to insert? Perhaps you want all the integers from 1 to
X, in which case you can use a numbers table to help you:
INSERT INTO SomeTable (z)
SELECT num
FROM numbers
WHERE num BETWEEN 1 AND @.x -- your parameter ;
--
David Portas|||If it's SQL2005, you can do without a numbers auxiliary table. To insert the
same integer X times:
with tmp as (
select 1 as a, 1 as b
union all
select a, b+1 from tmp where b < 100 -- or @.x
)
insert junk(a)
select a from tmp;
To insert integers from 1 to @.x:
with tmp as (
select 1 as a
union all
select a + 1 from tmp where a < 100 -- or @.x
)
insert junk(a)
select a from tmp;
Linchi
"David Portas" wrote:
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
> What data do you want to insert? Perhaps you want all the integers from 1 to
> X, in which case you can use a numbers table to help you:
>
> INSERT INTO SomeTable (z)
> SELECT num
> FROM numbers
> WHERE num BETWEEN 1 AND @.x -- your parameter ;
> --
> David Portas
>
>|||Hi Gerry!
You could try
INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
GO 10;
This will loop through all statements in the batch 10 times. That's assuming
that you want identical copies of the same row to be inserted of course. And
it doesn't work with variables unfortunately, as they get re-declared and
re-assigned in every loop.
Regards,
Jan
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
The "GO n" method will work with SQL Server tools like SSMS or SQLCMD but
not from application code. GO is a batch terminator recognized only by the
SQL Server tools and is not actually sent to the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Van der Eecken" <jkerner@.mweb.co.za> wrote in message
news:OBAl5ZaGIHA.4956@.TK2MSFTNGP06.phx.gbl...
> Hi Gerry!
> You could try
> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
> This will loop through all statements in the batch 10 times. That's
> assuming that you want identical copies of the same row to be inserted of
> course. And it doesn't work with variables unfortunately, as they get
> re-declared and re-assigned in every loop.
> Regards,
> Jan
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>>I want a insert statement to repeat X number of times (X will be a
>>variable
>> passed from a user interface)...any hints on how to code the sql'
>> Thanks!!!
>|||Andrew, how do I pass the variable to the stored proc? Do I use a select
statement to select it?...I want it all to fit into a single job...this is
what I have so far...but it only inserts a single record...
(In this example freeintfield_01 has a value of 100)
Select freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null
create proc sngen
@.freeintfield_01 int
as
while @.freeintfield_01>0
begin
insert into sngenerator (itemcode)
select itemcode
from absences
where type=206 and status=1 and freedatefield_05 is null and
freeintfield_01=@.freeintfield_01
set @.freeintfield_01=@.freeintfield_01-1
end
"Andrew J. Kelly" wrote:
> Put it in a stored proc with a WHILE Loop.
> CREATE PROC YourProc
> @.Loop INT
> AS
> WHILE @.Loop > 0
> BEGIN
> INSERT INTO Table VALUES (x)
> SET @.Loop = @.Loop - 1
> END
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
>|||Are you talking about a SQL Agent job? If so then like this:
DECLARE @.freeintfield_01 int
SET @.freeintfield_01 = (SELECT freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null )
EXEC dbo.sngen @.freeintfield_01
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> Andrew, how do I pass the variable to the stored proc? Do I use a select
> statement to select it?...I want it all to fit into a single job...this
> is
> what I have so far...but it only inserts a single record...
> (In this example freeintfield_01 has a value of 100)
> Select freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null
> create proc sngen
> @.freeintfield_01 int
> as
> while @.freeintfield_01>0
> begin
> insert into sngenerator (itemcode)
> select itemcode
> from absences
> where type=206 and status=1 and freedatefield_05 is null and
> freeintfield_01=@.freeintfield_01
> set @.freeintfield_01=@.freeintfield_01-1
> end
>
> "Andrew J. Kelly" wrote:
>> Put it in a stored proc with a WHILE Loop.
>> CREATE PROC YourProc
>> @.Loop INT
>> AS
>> WHILE @.Loop > 0
>> BEGIN
>> INSERT INTO Table VALUES (x)
>> SET @.Loop = @.Loop - 1
>> END
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >I want a insert statement to repeat X number of times (X will be a
>> >variable
>> > passed from a user interface)...any hints on how to code the sql'
>> >
>> > Thanks!!!
>>|||Yes, the loop works fine but it does not insert the data (itemcode)100 times,
just once....?
"Andrew J. Kelly" wrote:
> Are you talking about a SQL Agent job? If so then like this:
> DECLARE @.freeintfield_01 int
> SET @.freeintfield_01 = (SELECT freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null )
> EXEC dbo.sngen @.freeintfield_01
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> > Andrew, how do I pass the variable to the stored proc? Do I use a select
> > statement to select it?...I want it all to fit into a single job...this
> > is
> > what I have so far...but it only inserts a single record...
> > (In this example freeintfield_01 has a value of 100)
> >
> > Select freeintfield_01
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null
> >
> > create proc sngen
> > @.freeintfield_01 int
> > as
> > while @.freeintfield_01>0
> >
> > begin
> >
> > insert into sngenerator (itemcode)
> > select itemcode
> >
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null and
> > freeintfield_01=@.freeintfield_01
> >
> > set @.freeintfield_01=@.freeintfield_01-1
> > end
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Put it in a stored proc with a WHILE Loop.
> >>
> >> CREATE PROC YourProc
> >> @.Loop INT
> >>
> >> AS
> >>
> >> WHILE @.Loop > 0
> >> BEGIN
> >>
> >> INSERT INTO Table VALUES (x)
> >>
> >> SET @.Loop = @.Loop - 1
> >> END
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >I want a insert statement to repeat X number of times (X will be a
> >> >variable
> >> > passed from a user interface)...any hints on how to code the sql'
> >> >
> >> > Thanks!!!
> >>
> >>
>|||If the loop is executing 100 times and the data is not being inserted you
must have an issue with the select statement.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> Yes, the loop works fine but it does not insert the data (itemcode)100
> times,
> just once....?
> "Andrew J. Kelly" wrote:
>> Are you talking about a SQL Agent job? If so then like this:
>> DECLARE @.freeintfield_01 int
>> SET @.freeintfield_01 = (SELECT freeintfield_01
>> from absences
>> where type=206 and status=1 and freedatefield_05 is null )
>> EXEC dbo.sngen @.freeintfield_01
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
>> > Andrew, how do I pass the variable to the stored proc? Do I use a
>> > select
>> > statement to select it?...I want it all to fit into a single
>> > job...this
>> > is
>> > what I have so far...but it only inserts a single record...
>> > (In this example freeintfield_01 has a value of 100)
>> >
>> > Select freeintfield_01
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null
>> >
>> > create proc sngen
>> > @.freeintfield_01 int
>> > as
>> > while @.freeintfield_01>0
>> >
>> > begin
>> >
>> > insert into sngenerator (itemcode)
>> > select itemcode
>> >
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null and
>> > freeintfield_01=@.freeintfield_01
>> >
>> > set @.freeintfield_01=@.freeintfield_01-1
>> > end
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Put it in a stored proc with a WHILE Loop.
>> >>
>> >> CREATE PROC YourProc
>> >> @.Loop INT
>> >>
>> >> AS
>> >>
>> >> WHILE @.Loop > 0
>> >> BEGIN
>> >>
>> >> INSERT INTO Table VALUES (x)
>> >>
>> >> SET @.Loop = @.Loop - 1
>> >> END
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >> >I want a insert statement to repeat X number of times (X will be a
>> >> >variable
>> >> > passed from a user interface)...any hints on how to code the sql'
>> >> >
>> >> > Thanks!!!
>> >>
>> >>
>>|||Yes, I found it...thanks for your help!
Gerry
"Andrew J. Kelly" wrote:
> If the loop is executing 100 times and the data is not being inserted you
> must have an issue with the select statement.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> > Yes, the loop works fine but it does not insert the data (itemcode)100
> > times,
> > just once....?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you talking about a SQL Agent job? If so then like this:
> >>
> >> DECLARE @.freeintfield_01 int
> >>
> >> SET @.freeintfield_01 = (SELECT freeintfield_01
> >> from absences
> >> where type=206 and status=1 and freedatefield_05 is null )
> >>
> >> EXEC dbo.sngen @.freeintfield_01
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> >> > Andrew, how do I pass the variable to the stored proc? Do I use a
> >> > select
> >> > statement to select it?...I want it all to fit into a single
> >> > job...this
> >> > is
> >> > what I have so far...but it only inserts a single record...
> >> > (In this example freeintfield_01 has a value of 100)
> >> >
> >> > Select freeintfield_01
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null
> >> >
> >> > create proc sngen
> >> > @.freeintfield_01 int
> >> > as
> >> > while @.freeintfield_01>0
> >> >
> >> > begin
> >> >
> >> > insert into sngenerator (itemcode)
> >> > select itemcode
> >> >
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null and
> >> > freeintfield_01=@.freeintfield_01
> >> >
> >> > set @.freeintfield_01=@.freeintfield_01-1
> >> > end
> >> >
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Put it in a stored proc with a WHILE Loop.
> >> >>
> >> >> CREATE PROC YourProc
> >> >> @.Loop INT
> >> >>
> >> >> AS
> >> >>
> >> >> WHILE @.Loop > 0
> >> >> BEGIN
> >> >>
> >> >> INSERT INTO Table VALUES (x)
> >> >>
> >> >> SET @.Loop = @.Loop - 1
> >> >> END
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> Solid Quality Mentors
> >> >>
> >> >>
> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >> >I want a insert statement to repeat X number of times (X will be a
> >> >> >variable
> >> >> > passed from a user interface)...any hints on how to code the sql'
> >> >> >
> >> >> > Thanks!!!
> >> >>
> >> >>
> >>
> >>
>