Hi guys,
Is there a trivial way for looping through each record in a table
that's equivalent to plsql's
for r1 in (select * from tablename) loop
v_name := r1.name;
...
end loop
?
I've tried the cursor approach but realised I'll need to define a
variable for each column inside the table. So am currently looking for
an alternative.
Thanks in advance.If you have to loop, then curosor is the only way.
--
"Opal" wrote:
> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
>|||Am 2 May 2006 21:23:29 -0700 schrieb Opal:
> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
If you have SQL2000 or above you can use TOP.
Here an example, let's say table has a unique field called id, which starts
with a value > 0:
select @.x = 0, @.y = max(id) from table
while @.x < @.y begin
select top 1 @.x = id, @.name = field1 from table where id > @.x order by id
.. do something with @.name ...
end
bye, Helmut|||Hi Opal,
You only need to declare variables for the columns you want, so if you are
only interested in [name] then just select that on the select statement in
the cursor...
declare tabcur cursor for
select name
from tablename
declare @.name nvarchar(100)
open tabcur
fetch next from tabcur into @.name
while @.@.fetch_status = 0
begin
... processing
fetch next from tabcur into @.name
end
deallocate tabcur
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Opal" <anchi.chen@.gmail.com> wrote in message
news:1146630209.489346.65260@.j73g2000cwa.googlegroups.com...
> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
>|||Hi,
Thanks Helmut. My concern with this approach (which is similar to what
I've done but with the cursor approach) is because my table contains
more than 20,000 rows, this means I'll be executing the select
statement >20,000 times.
But thanks anyway. It seems like there's not other ways around it.
Regards|||Thanks Tony,
Yes, but because I'll need to address every single columns in my table
(which is more than 20).
What I'm trying to do is actually copying a table from another database
into 2 tables. For example, say if the primary table has 5 columns,
I'll need to insert the first 2 columns into table 1 and the last 3
columns into table 2. And hence need to loop through an entire table
to access every single column.
Thanks|||I usually table variable than cursor.
DECALRE @.t TABLE (ID Identity,...,)
INSERT @.t SELECT ..,FROM Employees
SELECT @.max_cnt=COUNT(*) FROM @.t
WHILE(@.i<=@.max_cnt)
BEGIN
SELECT .., FROM @.t WHERE ID=@.i
..,
SET @.i=@.i+1
END
"Opal"?? ??? ??:
> Hi guys,
> Is there a trivial way for looping through each record in a table
> that's equivalent to plsql's
> for r1 in (select * from tablename) loop
> v_name := r1.name;
> ...
> end loop
> ?
> I've tried the cursor approach but realised I'll need to define a
> variable for each column inside the table. So am currently looking for
> an alternative.
>
> Thanks in advance.
>|||Oh, you don't need a cursor or owt like that, just use two insert
statements...
insert yourtable1( col1, col2 )
select col1, col2
from database1.dbo.tablename
insert yourtable2( col3, col4, col5 )
select col3, col4, col5
from database1.dbo.tablename
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Anchi" <anchi.chen@.gmail.com> wrote in message
news:1146640265.804502.181830@.g10g2000cwb.googlegroups.com...
> Thanks Tony,
> Yes, but because I'll need to address every single columns in my table
> (which is more than 20).
> What I'm trying to do is actually copying a table from another database
> into 2 tables. For example, say if the primary table has 5 columns,
> I'll need to insert the first 2 columns into table 1 and the last 3
> columns into table 2. And hence need to loop through an entire table
> to access every single column.
> Thanks
>|||Thanks Hongju, this table variable is a feature that I've been
searching for the entire day without success.
I wonder if I can define it as an existing table such that I don't need
to list out the column types?
eg
DECLARE @.t TABLE <existing_table_name>
?
Thanks|||Thanks again Tony,
The examples you've shown me is exactly what I'm doing to avoid
declaring variables for each column.
However because yourtable1 has a new column with type Identity and
yourtable2 has a foreign key referencing yourtable1, I cannot just use
2 simple and clean sql statements.
I'll give Hongju's solution a try.
Thanks again, really appreciate your help.
