[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

loop through records in stored procedure

Posted on 2006-11-27
7
Medium Priority
?
8,003 Views
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.
0
Comment
Question by:Poppekop
[X]
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
7 Comments
 
LVL 10

Expert Comment

by:bret
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.

-bret
0
 
LVL 4

Author Comment

by:Poppekop
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?
0
 
LVL 19

Expert Comment

by:grant300
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,
Bill
0
What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

 
LVL 4

Author Comment

by:Poppekop
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
0
 
LVL 19

Accepted Solution

by:
grant300 earned 1000 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:
  SELECT *
     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:
  BEGIN
      DECLARE @work_count INT
      SELECT @work_count = COUNT(*) FROM tempdb..mywork

      BEGIN TRANSACTION

           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
              BEGIN
                  ROLLBACK
                  PRINT 'Failed inserting into target table'
                  RETURN -1
              END

            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
              BEGIN
                  ROLLBACK
                  PRINT 'Failed updating secondary target table'
                  RETURN -1
              END

      COMMIT

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

      DROP TABLE tempdb..mywork

  END

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.  www.sybase.com/ase15

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

Regards,
Bill
0
 
LVL 10

Expert Comment

by:bret
ID: 18029037
... and you can find the manuals here:  http://sybooks.sybase.com
0
 
LVL 4

Author Closing Comment

by:Poppekop
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...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

As much as Microsoft wants to kill off PST file support, just as they tried to do with public folders, there are still times when it is useful or downright necessary to export Exchange mailboxes to PST files. Thankfully, it is still possible to e…
Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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