Poppekop
asked on
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.
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.
ASKER
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?
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?
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
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
... and you can find the manuals here: http://sybooks.sybase.com
ASKER
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...
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