Tuesday, April 21, 2009

Auto Increment in Sybase

I had to use auto increment column in Sybase, I found we can use column as identity for it. Then I had a problem as I wanted to know last inserted value or id but I didn’t get answer today I was browsing to find how we can do so I found we can do it using Sybase global variable @@identity.


An easy way to insert a row into salesdetail after inserting a row into sales is to use the @@identity global variable to insert the IDENTITY column value into salesdetail. The @@identity global variable stores the most recently generated IDENTITY column value. For example:

begin tran
insert sales values ("6380", "04/25/97")
insert salesdetail values ("6380", @@identity, "TC3218", 50, 50)
commit tran
This example is in a transaction because both inserts depend on each other to succeed. For example, if the sales insert fails, the value of @@identity is different, resulting in an erroneous row being inserted into salesdetail. Because the two inserts are in a transaction, if one fails, the entire transaction is rejected

No comments: