I'm just getting up on SQL and I have a very basic issue. How would you
implement a "loop" or FOR/NEXT directive in SQL. I have looked around and do
not see anything that seems like a likely command. Thanks in advance for you
r
input.> How would you implement a "loop" or FOR/NEXT directive in SQL.
There is WHILE/BEGIN/END. However, usually, you would do what you need to
do WITHOUT resorting to loops. SQL is based fundamentally on treating data
as sets rather than iterations. If you give us more details about what you
are trying to accomplish, instead of deciding that you need to use loops,
you will probably get better help and will be on your way to thinking in
sets instead of singletons.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||TSQL has WHILE loops.
Hopefully I don't need to tell you that Iterating through a table or
result set in SQL isn't a good idea. Best practice is to write your
data manipulation as set-based code without loops. If you need help to
do that, then please come back with some more information of what you
are trying to do.
David Portas
SQL Server MVP
--|||We are making a big jump fromthe xBase world... my brain is still adjusting.
In my database I have tmpTable1...tmpTable99 and mastertable
I want to do an INSERT INTO mastertable for each tmpTable
Although syntactically incorrect, Something like...
FOR tblCount = 1 TO 99
INSERT INTO mastertable SELECT (fields) FROM tmpTable##
DROP table##
NEXT tblCount
"Aaron [SQL Server MVP]" wrote:
> There is WHILE/BEGIN/END. However, usually, you would do what you need to
> do WITHOUT resorting to loops. SQL is based fundamentally on treating dat
a
> as sets rather than iterations. If you give us more details about what yo
u
> are trying to accomplish, instead of deciding that you need to use loops,
> you will probably get better help and will be on your way to thinking in
> sets instead of singletons.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>|||In that case the real question is why do you have 99 separate tables to
start with? Apparently they are all the same structure so in the SQL
world it makes much more sense to have ONE table, if necessary with an
extra column for whatever attribute is represented by the number 1 -
99.
If this is just a one-off exercise then you can just cut and paste a
script of 99 INSERT statements. If this is something you feel the need
to do regularly then you should definitely reconsider your design - fix
whatever process creates these tables so that it just creates one table
instead.
If you are stuck with 99 separate tables and don't want to hard-code
the names then you will be forced to use a messy kludge with dynamic
SQL. Unlike xBase you can't easily parameterize table names in SQL
Server. Instead you have to build a command string and execute it like
a macro using the EXEC statement. That's one reason why good logical
database design is so important.
Dynamic SQL introduces a number of problems to do with performance,
scalability, maintenance and security. Don't go there if you can
possibly avoid it, and read the following article first:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--|||> In that case the real question is why do you have 99 separate tables to
> start with?
Hey, at least it seems he's trying to fix it. :-) And he can generate his
list of INSERT statements pretty easily, e.g. using Query Analyzer:
DECLARE @.i TINYINT
SET @.i = 1
WHILE @.i <= 99
BEGIN
PRINT 'INSERT mastertable(column_list) SELECT column_list FROM
tmpTable'+RTRIM(@.i)
SET @.i = @.i + 1
END
But I agree with everything else you said... hopefully this is a one-time
thing and not something that is going to become a part of the app.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||>> We are making a big jump fromthe xBase world... my brain is still
adjusting. <<
It will take at least a year to un-learn the old mental models.
That would be a UNION of all the tables. But this sounds like your
schema and appraoch are wrong. Instead of 99 identical tables, why not
one table with an integer column that holds whatever information is
need to show the source of the data?|||The WHILE loop worked nicely. Thanks for the input!
"The VanDerbeck Group" wrote:
> I'm just getting up on SQL and I have a very basic issue. How would you
> implement a "loop" or FOR/NEXT directive in SQL. I have looked around and
do
> not see anything that seems like a likely command. Thanks in advance for y
our
> input.
