We have an Oracle application that uses a standard pattern to populate surrogate keys. We have a series of extrinsic rows (that have specific values for the surrogate keys) and other rows that have intrinsic values. We use the following Oracle trigger snippet to determine what to do with the Surrogate key on insert:

'IF :NEW.SurrogateKey IS NULL THEN

SELECT SurrogateKey_SEQ.NEXTVAL INTO :NEW.SurrogateKey FROM DUAL;

END IF;'

If the supplied surrogate key is null then get a value from the nominated sequence, else pass the supplied surrogate key through to the row.

I can't seem to find an easy way to do this is T-SQL. There are all sorts of approaches, but none of which use the notion of a sequence generator like Oracle and other SQL-92 compliant DBs do.

Anybody know of a really efficient way to do this in SQL Server T-SQL? BTW we're using SQL Server 2008 if that's any help.

TIA, Paolo