Geek Answers Handbook

Get id column values ​​on INSERT

I am using SQL Server 2008 to create a procedure.

I use the following sql statement to insert into the audit table

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

I use scope_identity()to get the id column from the listuser table and insert the id column in another log table

If the select statement contains more than one value, how do I get the identifier value of both columns and insert into the log table?

Thanjs

+5
source share
1 answer

If you need to commit multiple inserted authentication values, I would use a sentence OUTPUT:

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'

ListUser , INSERT, @TableOfIdentities

OUTPUT MSDN

, , :

SELECT * FROM @TableOfIdentities

: - , "" SQL Server, , - - :

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'
+12

More articles:


All Articles