Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Before Inserting I want MAX(Id)/SELECT @@identity

Author  Topic  i4udear Starting Member 2 Posts Posted - 2001-01-05 : 09:00:00 Hi SQLTeam, Can you please help me in this matter? Urgent The problem is: I have two tables one is "TransMain" and the other "TransDetails". In "TransMain" auto increment field PK called "Id". The same "Id" is a FK in "TransDetails". While inserting the details into both tables I want to rollback the transaction if any error occur in insertion. To Insert a record into "TransDetails" table we need the "Id" (FK). Without CommitTrans of Insert statement into "TransMain" I can not get that sequence number. If I commit that, I can not rollback if any error occur during insertion of "TransDetails". So right now I'm taking the max(Id) from "TransMain" and adding plus one and inserting into both the tables. If error occurs then RollbackTrans will execute, otherwise CommitTrans. I know this is not the way of doing and also I am facing the problem when if the last record is deleted. i.e., Let us think there is total 12 records in "TransMain" and the "Id" is also 12. If I Insert a record Id automatically increase to 13. And also the max(Id)+1 works as a FK for "Id" in "TransDetails" table. The problem is, if the last two records are deleted, then max(Id)+1 will give 11(10+1) and the auto increment gives 13. Which is wrong. Is there any way to get the auto increment value before inserting the record. So please give me a solution as soon as possible. Thanks and Regards, Ramana Murthy Reddicharla