I have the following two tables (among many) in an OLTP database:
TABLE NAME: BusinessEntity
BusinessEntityID, int, IDENTITY(1,1), NOT NULL, PRIMARY KEY
BusinessEntityType, nvarchar(10), NULL
TABLE NAME: Person
BusinessEntityID, int, NOT NULL, PRIMARY KEY, FOREIGN KEY to BusinessEntity.BusinessEntityID
FirstName, nvarchar(30), NULL
LastName, nvarchar(30), NULL
There is a 1-to-1 Identifying Relationship between the BusinessEntity and Person tables.
When a new customer needs to be added to the database, I execute the following T-SQL statement:
INSERT INTO BusinessEntity (BusinessEntityType) VALUES (‘Customer’);
What I don’t know how to do is retrieve the value of BusinessEntity.BusinessEntityID that was automatically assigned to the new customer so I can then append it to Person.BusinessEntityID.
While I know I can execute:
SELECT MAX (BusinessEntityID) FROM BusinessEntity;
there is no guarantee that the BusinessEntityID will be the one associated with the new customer I just added because it is possible that other new customers can be added to the BusinessEntity table by other users during the interim period between
the time I execute the previously mentioned INSERT command and then SELECT command.
Any help/guidance in resolving this issue would be greatly appreciated. Thanks.
View Complete Post