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 2005 Forums  Transact-SQL (2005)  Insert Locks required?

Author  Topic  Tassadar Starting Member 1 Post Posted - 2009-08-28 : 05:08:15 Hi all, sorry if this has been gone over before but i have the following problem. I need to insert data into a table where the primary key is just a sequence that goes up by 1 each new record. This must be done manually in the program though as it is not set as Identity(and i can't change this as 3rd party app). When i insert a new row i need to use that value for primary key to insert data into a second table also. My thoughts on how to do were Select max value +1 from table Insert records using this new value to table 1 Insert records using this new value to table 2 My problem is that between my select statement and the insert, the program(which has multiple users) could have inserted more records which would cause the inserts to fail. I thought about locking the table, but have been told that affects performance, so is there any other way to do this? or is locking the only option? Any help greatly appreciated. Andy