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?
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()