Sequences

[hmckillop]

Hello

I have the following statement

INSERT INTO TableA (OID, ReferenceField)
SELECT MyOID, <Sequence>, DependantField, Otherfields
FROM TableB

I need the sequence to be made up of the current date and a number held in another table which should be updated each time its called. The sequence numbers are held in a table and are dependant on the additional field &quot;DependantField&quot
e.g.
Sequence table
SEQUENCENAME(DependantField) VALUE
ABC 1
CDE 1000

Ideally I wanted a function which would take in the dependant field and would return the next in the sequence while updating the sequence table e.g
INSERT INTO TableA (OID, ReferenceField)
SELECT MyOID, UFN_fnGetNextSequence(DependantField), DependantField, Otherfields
FROM TableB

But I cant include an update statement within the function.

Any help much appreciated,

PS If even you can give me some code to increment a counter while inserting without using identity fields.

Thanks