Use table with records as variables

Posted on 2007-10-15
Last Modified: 2010-03-19
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)
Question by:donnatronious
    LVL 17

    Expert Comment

    Use a cursor.
    LVL 35

    Expert Comment

    by:David Todd

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

    A curor can do what you want

    declare c_Cursor
    for select
      , 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

      -- rest of loop

    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 ...

    Author Comment

    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)

    LVL 35

    Accepted Solution


    select a.*
    from SimEDMSQL04.EPMSSub.dbo.DP_StreetAddresses a
    where a.ID in
      select min( ai.ID )
      from SimEDMSQL04.EPMSSub.dbo.DP_StreetAddresses ai
      group by, ai.zip5, ai.sip4

    Assuming that there is an id or something similar.


    Author Comment

    Damn Your Slick!!!!
    LVL 35

    Expert Comment

    by:David Todd

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now