In an INSERT statement, a SELECT statement returning a scalar value was allowed in the VALUES clause.
The INSERT statement cannot have a SELECT statement in the VALUES clause as one of the values to be inserted. The version 6.x supportability is available only when the compatibility setting is equal to 60 or 65.
I havent tried doing it.... Usually it need to be backward compatible... but as there is a list of features in older one which is not there in newer. If u have gone thru all the supported links in that u could see that new version Trigger can be used for the sane purpose.... Like if we need to get a scalar value assigned to variable after inserting use trigger & while using that 'SET NOCOUNT' ON for this scenario... something like that.....
create trigger test on TableName for insert, update, delete
SET NOCOUNT ON
"select @TableName = For Last Inserted value..................."
If the value you would like to be returned is the primary id field then you can use 'SCOPE_IDENTITY()'.
INSERT INTO dbo.TL1_MyTestTable
IF @@Error <> 0
RAISERROR('The insert did not work dude!',16,1)
SET @Primary_ID = SCOPE_IDENTITY()
This way you can use the id returned by SCOPE_IDENTITY() to return the whole row within the stored proc if you need to:
Select * from dbo.TL1_MyTestTable
Where Primary_ID = @Primary_ID