How to insert multiple records and get the identity value?

I’m inserting multiple records into a table A from another table B. Is there a way to get the identity value of table A record and update table b record with out doing a cursor?

Create Table A
(id int identity,
Fname nvarchar(50),
Lname nvarchar(50))

Create Table B
(Fname nvarchar(50),
Lname nvarchar(50),
NewId int)

Insert into A(fname, lname)
SELECT fname, lname
FROM B

I’m using MS SQL Server 2005.

Answer

MBelly is right on the money – But then the trigger will always try and update table B even if that’s not required (Because you’re also inserting from table C?).

Darren is also correct here, you can’t get multiple identities back as a result set. Your options are using a cursor and taking the identity for each row you insert, or using Darren’s approach of storing the identity before and after. So long as you know the increment of the identity this should work, so long as you make sure the table is locked for all three events.

If it was me, and it wasn’t time critical I’d go with a cursor.