Решение:

использовать output

declare @ordID int
DECLARE @MyNewIdentityValues table(myidvalues int)

insert into attr107(objectClassID, p541, p542, p545)
output inserted.ObjectID into @MyNewIdentityValues
values(107, (select objectID from attr100 where p495=7002), getdate(), 1);
select top 1 @ordID = myidvalues from @MyNewIdentityValues

select p539 from attr107 where objectID=@ordID