Get identity column values upon INSERT

Question

I am using SQL Server 2008 to create a procedure.

I am using the following sql statement to insert into a audit table

 insert into  Listuser
 (
   UserID,
   ListID,
   AuditCreated
  )
   select
   UserID,
   ListID,
    GETDATE()
 from  ListUser where Surname='surname'

I am using scope_identity() to get the identity column from the listuser table and insert the identity column to another logs table

If the select statement contains more than 1 value, how to get the identity value of both columns and insert into logs table?

Thanjs

Answer 1

If you need to capture multiple identity values being inserted, I'd use the OUTPUT clause:

DECLARE @TableOfIdentities TABLE (IdentValue INT)

INSERT INTO dbo.Listuser(UserID, ListID, AuditCreated) 
  OUTPUT Inserted.ID INTO @TableOfIdentities(IdentValue)
  SELECT
      UserID, ListID, GETDATE()
  FROM 
      dbo.ListUser 
  WHERE
      Surname = 'surname'

This will insert all rows into your ListUser table, and it will output all identities generated by this INSERT into the @TableOfIdentities table variable

Read more about the OUTPUT clause on MSDN

These values are inserted into the table variable, and you can select them out from that table variable after the insert, and do whatever you need to do with them:

SELECT * FROM @TableOfIdentities

Update: the use of the table variable here is just as an example - of course you can also output the data into a "normal" table in SQL Server - and you can also output multiple values for each inserted row, if you need that - so you can have something like:

INSERT INTO dbo.Listuser(UserID, ListID, AuditCreated) 
  OUTPUT Inserted.ID, Inserted.UserID, Inserted.SurName, GETDATE() 
       INTO AuditTable(IdentValue, UserID, Surname, InsertDate)
  SELECT
      UserID, ListID, GETDATE()
  FROM 
      dbo.ListUser 
  WHERE
      Surname = 'surname'