• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

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)
  • 3
  • 2
1 Solution
Chris MangusDatabase AdministratorCommented:
Use a cursor.
David ToddSenior DBACommented:

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 ...
donnatroniousAuthor Commented:
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)

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

David ToddSenior DBACommented:

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.city, ai.zip5, ai.sip4

Assuming that there is an id or something similar.

donnatroniousAuthor Commented:
Damn Your Slick!!!!
David ToddSenior DBACommented:

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now