How to capture the unique identifier after the insert

Question

I have to create new email records in an existing table in the joining table I would like to update a field that would denote that this is a new record.

Example:

INSERT INTO dbo.email.email (dbo.email.eml_address, dbo.email.eml_customer_key)
SELECT new_email, new_customer_key
FROM NEW_TABLE

Update dbo.email_ext
Set dbo.email_ext.new_eml = '1'
Where dbo.email_ext.eml_key_ext = 'Recently create key from insert statement shown above'

score 4 · Accepted Answer · answered Jul 15 '14 at 19:20

You'll want to use the SCOPE_IDENTITY() value, this will contain the ID of the record just created, but only one.

Assuming you're handling ONE record:

DECLARE @ID INT

INSERT INTO dbo.email (eml_address, eml_customer_key)
SELECT new_email, new_customer_key
FROM NEW_TABLE

SET @ID = SCOPE_IDENTITY()

Update dbo.email_ext
Set new_eml = '1'
Where eml_key_ext = @ID

If you're inserting multiples you need to output the list into a table (in this case a table variable) and you can update them all at once.

DECLARE @myIDs TABLE (NEWID INT)

INSERT INTO dbo.email (eml_address, eml_customer_key)
OUTPUT inserted.ID INTO @myIDs 
SELECT new_email, new_customer_key
FROM NEW_TABLE

Update t
Set new_eml = '1'
from dbo.email_ext t
join @myIDs m
   on t.eml_key_ext = m.ID

score 2 · Answer 2 · answered Jul 15 '14 at 19:20

Use an OUTPUT clause to capture autogenerated ids/guids/defaults/etc.

CREATE TABLE #test (
  id int identity(1,1) primary key,
  uid uniqueidentifier DEFAULT NEWID(),
  value varchar(max)
)

INSERT #test (value)
OUTPUT inserted.*
SELECT 'test'


id          guid                                 value
----------- ------------------------------------ ---------
1           72B70577-2679-4C2A-A575-62D30807B9D2 test

(1 row(s) affected)

How to capture the unique identifier after the insert

2 Answers2