"for" loop

[adam]

hello

Does exist in SQL language "for" loop ? If yes, what syntax does it has ?

best wishes
Adam

[Simon Hayes]

No - see WHILE and "Cursors" in Books Online. But in SQL, a loop is
often not a good solution - people may use them when they should use a
single, set-based statement such as UPDATE or DELETE.

Of course, there are times when a while or cursor loop is the best
solution, so you might have a good reason to do this - if you can give
more details of what you need to achieve, someone may be able to
comment on whether a loop is appropriate for you or not.

Simon

[David Portas]

Mostly it pays to find a set based solution rather than write loops. If
you need to loop then TSQL has WHILE.

--
David Portas
SQL Server MVP
--

[adam]

I try to substitute "for" loop by this excample:

declare @i int
while (@i<10)
begin
insert into dbname.dbo.table_name values @i
@i=@i+1
end

but in line @i=@i+1 ocured an error. Why ?
I know - it is stupid reason to use "for" loop , but what shoul I changed to
work this example ?
What are better ways to do such task ?

[David Portas]

A common solution is to keep a table of numbers in your database
(single column of numbers from 0 to some very large number). This can
help avoid loops in many places:

INSERT INTO table_name (...)
SELECT num, ...
FROM Numbers
WHERE num BETWEEN 1 AND 10

Of course you'll still probably use a loop to populate the Numbers
table, but that only has to be done once and at install time, not at
runtime.

--
David Portas
SQL Server MVP
--

[David Portas]

Numbers table:

CREATE TABLE numbers (num INTEGER NOT NULL CONSTRAINT pk_numbers
PRIMARY KEY) ;

INSERT INTO numbers (num) VALUES (0) ;

WHILE (SELECT MAX(num) FROM numbers)<65535
INSERT INTO numbers
SELECT num+(SELECT MAX(num)+1 FROM numbers)
FROM numbers ;

--
David Portas
SQL Server MVP
--

[Simon Hayes]

As David suggested, a numbers table is definitely a useful tool:

http://www.aspfaq.com/show.asp?id=2516

A calendar table is another good one:

http://www.aspfaq.com/show.asp?id=2519

Simon

[jsfromynr]

Hi David,
We can do this simple trick

Select identity(int,1,1) MyId into MyTable from
anySystemTable,anySystemTable,anySystemTable .......

With warm regards
Jatinder Singh

[Erland Sommarskog]

adam (er******@wp.pl) writes:

I try to substitute "for" loop by this excample:

declare @i int
while (@i<10)
begin
insert into dbname.dbo.table_name values @i
@i=@i+1
end

but in line @i=@i+1 ocured an error. Why ?

You need to say SET or SELECT in front. I prefer SELECT, as you can assign
more that one variable in one bang:

SELECT @a = 1, @b = @c + d, @t = NULL


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

Here is a way to gemerate the Sequence table without proprietary code
and create the rows in parallel instead of one at a time.

WITH
(SELECT 0
UNION ALL
SELECT 1
..
UNION ALL
SELECT 9) AS Digits(digit)
SELECT D1.digit + 10*D2.digit + 100*D3.digit + 1000*D4.digit + ..
FROM Digits AS D1, Digits AS D2, Digits AS D3, Digits AS D4, ..
WHERE (D1.digit + D2.digit + D3.digit + D4.digit + ..) > 0;

IDENTITY has be done one at a time, while this can be parallelized and
can generate numbers in sets. The CTE can be repalced with a TABLE or
VIEW.