loop through records in stored procedure

Posted on 2006-11-27
Last Modified: 2010-04-21
I'm looking for a way to loop through records in a table and insert the values plus a number which value I have to look up into another table.

Can anyone help me with the sybase syntax for this? Any way will do, as long as it works :)

I'm pretty new at sybase, but do know a lot of SQL and scripting from MS Access and SQL server.
Question by:Poppekop
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 10

Expert Comment

ID: 18020066
Is a loop really necessary?

How do you look up the value in the other table? Is it based on some values
in the first table?  If so, why not use a join?

insert newtable select a.c1, a.c2, a.c3, b.lookupvalue
from mytable a, mylookuptable b
where a.c1 = b.c1

If you really do have to loop, you would use a cursor to fetch one row at
a time.  See the DECLARE and FETCH commands in the reference manual.


Author Comment

ID: 18020159
it has to be a unique value for each record and it has to be updated in the other table for every entry I make.

I don't have a reference manual, I don't even have access to the sybase database, but I do have to create a script.

Do you have an example of these fetch and declare en cursor where you enter a new record into a table?
LVL 19

Expert Comment

ID: 18020725
Is the update of the other table absolute?  That is, do you have to do it every time no matter how/when an entry is made in the first table?

If that is the case, I would put an INSERT trigger on the first table that makes the UPDATE on the second table.  Then you can take Bret's suggestion and use an INSERT/SELECT that joins the two tables and away you go.

Clean, fast, effecient.  It also has the advantage of keeping everything in the context of a single transaction so a failure causes a nice clean rollback and leaves no mess to clean up.

BTW, forget everything you think you know about SQL that you learned with Access.  That dialect is so far out in left field you would be better off without it.  the SQL Server syntax is almost identical though.

For a good book on SQL, get a copy of Joe Celko's 'SQL For Smartties'.  It picks up where most of the introductory texts leave off.

Best of luck,
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Author Comment

ID: 18026584
ok so I can insert records and change values, but I cannot change the datamodel of put triggers on tables or anything like that. If I have to do that I have to pay about 12000 euro. I don't even have sybase, all I can do is supply scripts and a csv file
LVL 19

Accepted Solution

grant300 earned 250 total points
ID: 18028871
So you will be loading the data into a table using BCP?

I would load the data into a table created specifically for the load.  You can create that table in a script then drop it at the end of processing.  Something like:
  CREATE TABLE tempdb..mywork AS (col1 datatype, col2 datatype, etc)

Another way to do it if your CSV file matches an existing target table is to do this:
     INTO tempdb..mywork
    FROM prod_trgt_tbl
  WHERE 1=2
This will create a mirror image of the target application table but not put any data in it.

Next, BCP the CSV file into tempdb..mywork

Finally, create a stored procedure that does the following:
      DECLARE @work_count INT
      SELECT @work_count = COUNT(*) FROM tempdb..mywork


           INSERT INTO prod_trgt_tbl
           SELECT blah blah blah
             FROM tempdb..mywork, prod_reference_tbl
           WHERE etc, etc

           IF @@ROWCOUNT <> @work_count OR @@ERROR <> 0
                  PRINT 'Failed inserting into target table'
                  RETURN -1

            UPDATE prod_other_tbl
                  SET some_columns = some_other_columns
               FROM prod_other_tbl, tempdb..mywork, prod_trgt_tbl
            WHERE  etc, etc

           IF @@ROWCOUNT <> @work_count OR @@ERROR <> 0
                  PRINT 'Failed updating secondary target table'
                  RETURN -1


      SELECT 'Succesfully loaded and processed with rowcount = ', @work_count

      DROP TABLE tempdb..mywork


This has the advantage of running cleanly with no change of leaving partial results in the database and, depending on your skill with T-SQL, has the potential to give you much more information should an error occur.  Running raw SQL statements from shell scripts is very hard to debug.

BTW, go to the Sybase web site now and download the developers edition.

It will make mocking this up and testing it much easier.

LVL 10

Expert Comment

ID: 18029037
... and you can find the manuals here:

Author Closing Comment

ID: 31406149
better late than never? I never got it to work because of limitations of the tool I had to use, but it looks workable and you deserve the points...

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The conference as a whole was very interesting, although if one has to make a choice between this one and some others, you may want to check out the others.  This conference is aimed mainly at government agencies.  So it addresses the various compli…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

717 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