asked on

Use table with records as variables

usually i make a column, seq, that is basically an autonumber whenever I want to loop through a table and use the records as variables. This allows me do as below. My question is what if I just wanted to loop through the table and do something with each of the records, but I didn't have a unique column to use like I am using the seq column below? How is this usually handled? Lets say the table just has names and addresses, but no unique key? I just want to feed the table from top to bottom?

      --Loop through variables to insert error counts

      set @seq = 0

      while @seq is not null begin
            select @seq = min(seq)
            from CMvCM2.dbo.DEMO_Analysis_Var
            where seq > @seq

            --Populate Variables
            if @seq is not null begin
                  select @attr = attr,
                              @bitmask = bitmask,
                              @CM = CM,
                              @CMD = CMD,
                              @CM2 = CM2,
                              @CM2D = CM2D
                  from CMvCM2.dbo.DEMO_Analysis_Var
                  where seq = @seq

            --Errors Insert
                        set @sql = 'Do some stuff'

                        exec (@sql)
                  end
            end

Chris Mangus

Use a cursor.

David Todd

Hi,

What are you doing that you need to loop through a table?

A curor can do what you want

declare c_Cursor
for select
attr
, bitmask
, CM
, cmd
, cm2
, cm2d
from CMvCM2.dbo.Demo_Analysis_Var

open cursor c_Cursor
while 1 = 1 begin
fetch next from c_Cursor into @attr, @bitmask, @CM, @CMD, @CM2, @CM2D

if @@fetch_status != 0
break;

-- rest of loop
end

Regards
David

PS Cursors aren't good in terms of performance, and often there is a better way to code, hence questions about what you are doing ...

donnatronious

ASKER

Here is the problem. I need an example of a full record from SimEDMSQL04.EPMSSub.dbo.DP_StreetAddresses
for each unique combination of city, zip5 and zip4.

So I already have a unique city, zip5, zip4 combination table which is here dbo.RS_PS_Driver.

So for each record in my unique combination table, i need to get ONE whole record from SimEDMSQL04.EPMSSub.dbo.DP_StreetAddresses. It doesn't matter which one.

It should be something like this.

      declare @city varchar(256)
      declare @zip5 char(5)
      declare @zip4 char(4)

      while select * from dbo.RS_PS_Driver is not null
            --Populate Variables
                  select @city = city,
                              @zip5 = zip5,
                              @zip4 = zip4
                  from dbo.RS_PS_Driver

            --Example Insert
                        set @sql = 'insert into dbo.DP_StreetAddresses
                                          select top 1
                                          from SimEDMSQL04.EPMSSub.dbo.DP_StreetAddresses
                                          where STDErrorCode is null
                                          and STDCity = ' + @city + '
                                          and STDZip5 = ' + @zip5 + '
                                          and STDZip4 = ' + @zip4 + ''

                        exec (@sql)
                  end
            en

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

donnatronious

ASKER

Damn Your Slick!!!!

David Todd

Hi,

Thanks for the grade and comment.

It does take a different way of thinking about problems from a row based loop to a set based solution.

Generally a set-based solution is preferred to a row based solution, and cursors do have additional overhead on the loop.

Do be aware that sp_MSForEachTable and sp_MSForEachDB are implimented as cursors.

Regards
David