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
Solved

loop through records in stored procedure

Posted on 2006-11-27
7
7,942 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
  • 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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 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:
  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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Is your phone running out of space to hold pictures?  This article will show you quick tips on how to solve this problem.
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 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