Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8010
  • Last Modified:

loop through records in stored procedure

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
Poppekop
Asked:
Poppekop
  • 3
  • 2
  • 2
1 Solution
 
bretCommented:
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
 
PoppekopAuthor Commented:
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
 
grant300Commented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
PoppekopAuthor Commented:
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
 
grant300Commented:
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
 
bretCommented:
... and you can find the manuals here:  http://sybooks.sybase.com
0
 
PoppekopAuthor Commented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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