Below is my SQL Server stored procedure, I'm getting primary Key Violation on Table1 when there are concurrent requests. How to solve this issue? Please advice.
Declare @@QuotationNo nvarchar(50)
Select @@QuotationNo = Max(fQuotationNo)+1 from Table1
Insert into Table1 Values(@@QuotationNo, value1, value2)
Insert into Table1_Child1 Values(@@QuotationNo, value3, value4)
Insert into Table1_Child2 Values(@@QuotationNo, value5, value6)
Insert into Table1_Child3 Values(@@QuotationNo, value7, value8)
Since there are millions of records in all tables it takes some time to insert and commit the first request, meanwhile next request generate the same max value from table 1.