Q:

How do I insert an Identity column rather than a constant into a table in which the column is not an identity column?

CREATE PROCEDURE spCustomerDetails
    @FirstName NVARCHAR(30),
    @LastName NVARCHAR(30),
    @Phone CHAR(30),
    @Email NVARCHAR(30)
AS
BEGIN
    BEGIN TRY
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

        BEGIN TRANSACTION
            INSERT INTO Person.Person (BusinessEntityID, PersonType, NameStyle,Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, AdditionalContactInfo)
            VALUES (20778, 'SC', 0, 'NULL', '@FirstName', '@MiddleName', '@LastName', 'NULL', '0', 'NULL');

            INSERT INTO Person.PersonPhone(BusinessEntityID, PhoneNumber, PhoneNumberTypeID)
            VALUES (20778, '@Phone', 2);

            INSERT INTO Person.EmailAddress (BusinessEntityID, EmailAddressID, EmailAddress)
            VALUES (20778, '1', '@Email');

            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION

        PRINT 'Roll back transaction'
    END CATCH
END

I don't intend to insert the value, 20778, into the columns of BusinessEntity. How do I call it from the original table (Person.BusinessEntity), where it is an identity column, using the last insert into these tables?

A:

You can use MAX:
DECLARE @id int = (select max(BusinessEntityId) From Person.BusinessEntity)

