[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • 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)

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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