When you use "TabLockx" table hint, It definitely place a Exclusive(x) lock on your table. Other trabsaction should wait until the existing complete.
So, As 'T.saravanan' said, IDENTITY can give you a sollution.
1. "RollNo" In your "students" table can be an IDENTITY.
2. When insert a new student's information, You should pass all the values except the IDENTITY column (RollNo), so the "RollNo" will be automatically generated and inserted on behalf of you.
3. You can use these Insert activity in a Stored Procedure
4. Return the Generated/Inserted Identity values(using @@Identity
) as a RETURN / OUTPUT from stored procedure and use the IDENTITY value in your application...
1.To denfine an IDENTITY on your existing table, You should DROP the table and Recreate the same with IDENTITY.
2. If you want to maintain the existing data then, Just copy the data to some other table....
3. Create the Student table with IDENTITY and force the data from the backup table to Student table.
SET IDENTITY_INSERT Student ON
INSERT Student(RollNo,Column1, Column2,...)
SELECT RollNo,Column1, Column2,... FROM Student_Backup
SET IDENTITY_INSERT Student OFF
4. Now you can proceed your own way...
CheersGaneshsvelu, if this helps please login to Mark As Answer. | Alert Moderator