Get PK ID of inserted row and insert into another table

Im inserting a row into a table.

I want to get the ID of the row inserted.

So I can update the a table that tracks history.

 

CREATE procedure [dbo].[InsertCompany]


@ConsultantID int,
@ConsultantName nvarchar(200),
@CompanyName nchar(100)



as
INSERT INTO
tblCommentCompany 
(
    added_date ,
    consultant_id ,
    consultant,
    company_name
)

VALUES 
(
GETDATE (),
@ConsultantID ,
@ConsultantName ,
@CompanyName
)


INSERT INTO tblCompanyHistory
(
his_action,
consultant_id,
consultant,
added_date,
company_id

)

VALUES

(
'Created',
@ConsultantID,
@Consultant,
GETDATE(),
????????????????(How DO i get the company ID)
)


GO

Select allOpen in new window