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,
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button


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

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.

Question has a verified solution.

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

Suggested Solutions

The world seems to conceive of a curious bubble separating IT from “the business.”  More so than just about any other pursuit in the commercial world, people think of IT as some kind of an island.
We have put together a white paper that aims to explain how MSPs can both improve their offering and ease the pain of after-hours service by: -Suggesting changes to workflow -Indicating how to rework policy to suit your team -Providing ConnectW…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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