sqldb2

db2 IDENTITY_VAL_LOCAL() behaviour

I would like to retrieve the last ID created for an identity column right after an insert and I want to use IDENTITY_VAL_LOCAL(). During my tests I have 2 different behaviors.

When I use an instruction like this it works fine.

 INSERT INTO(fld1,fld2, ..) VALUES (val1,val2 )

but when I call a more complex insert, NULL is returned to me. I have something like:

 INSERT INTO(fld1,fld2, ..) SELECT a,b,c,.. FROM tbl1,tbl2.. WHERE ...

Is it because it only really works with INSERT/VALUE calls or should I have a look at something else?

Solution

  • Try this

    INSERT INTO Tablename(fld1,fld2, ..) VALUES (val1,val2 )

SELECT SCOPE_IDENTITY()

INSERT INTO Tablename(fld1,fld2, ..) SELECT a,b,c,.. FROM tbl1,tbl2.. WHERE ...

SELECT SCOPE_IDENTITY()