Retrieving the identity of a newly inserted row in a database (Access and SQL server)

Hi,

I've wondered about thsi for a while and would like ot get a stragiht answer. Googling and looking through past threads in this forum aren't helping me.

In pipeline Pilot, using the SQL components (Insert, Select or General - I'm not fussy), i'd like to append to my current data record the Identity of the row into which it is inserted in a database.

At the moment I'm using an INSERT component:

INSERT into (Column1,Column2,...)

VALUES (?, ?, ...)

followed by a SELECT component:

Select @@Identity from


This seems to work but forums on the web discourage the usage of  the @@Identity command. (and just now I've seen varying numbers of records - albeit identical - being returned using this statement)

It also seems clunky and I wonder if the statements can't be combined into a single component. I've tried this but haven't been able to work out the pairing of the syntax with the SQL Parameter Mapping and SQL Parameter Types.

Yet another thing I'm not sure about is if the syntax / functionality is different when quering a DB hosted on a SQL Server or MSAccess.

A Dummy's guide on how to do this would be most appreciated.

Oh while I'm at it - if I have to resort to usinghte @@Identity command is there a way to set the name of the output property from within hte SQL syntax. Currently it comes out variously named "Expr100/3000" and I have to rename using

rename(ithpropertyname(numproperties()),'MyFavPropName').

I'm still on PP v8.5 btw


As always many thanks for the advice and cooment,

Tris