How can I insert a newly generated IDENTITY value into a related table with other fields?

Question

I have a data table that contains a name and a social security number. I want to insert the name into a table with an identity field, then insert the ssn with that new identity field value into another table.

Below are the tables:

CREATE TABLE [data_table] 
(
    [name] [varchar](50) NOT NULL,
    [ssn] [varchar](9) NOT NULL,
)

CREATE TABLE [entity_key_table] 
(
    [entity_key] [int] IDENTITY(1000000,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
)

CREATE TABLE [entity_identifier_table] 
(
    [entity_identifier_key] [int] IDENTITY(1000000,1) NOT NULL,
    [entity_key] [int] NOT NULL,
    [ssn] [int] NOT NULL,
)

This query works but doesn't link entity_key in [entity_key_table] TO ssn in [entity_identifier_table]:

INSERT INTO entity_key_table (name)
OUTPUT [INSERTED].[entity_key]
INTO [entity_identifier_table] (entity_key)
    SELECT [name]
    FROM [data_table]

This is what I want to do, but it doesn't work.

INSERT INTO entity (name)
OUTPUT [INSERTED].[entity_key], [data_table].[ssn]
INTO [entity_identifier] (entity_key,ssn)
    SELECT [name]
    FROM [data_table]

Answer 1

Rewriting my answer based on your requirements and the articles you linked. I think you can get that behavior doing something like this. I admit, I have never seen a merge on something like 1 != 1 like the article suggests, so I would be very cautious with this and test the bajeezes out out of it.

FWIW, it looks like during an INSERT, you can't access data that's not in the inserted virtual table, but updates (and apparently MERGE statements) can.

if object_id('tempdb.dbo.#data_table') is not null drop table #data_table
create table #data_table
(
    [name] [varchar](50) NOT NULL,
    [ssn] [varchar](9) NOT NULL,
)

if object_id('tempdb.dbo.#entity_key_table') is not null drop table #entity_key_table
create table #entity_key_table
(
    [entity_key] [int] IDENTITY(1000000,1) NOT NULL,
    name varchar(50)
)

if object_id('tempdb.dbo.#entity_identifier_table') is not null drop table #entity_identifier_table
create table #entity_identifier_table
(
    [entity_identifier_key] [int] IDENTITY(2000000,1) NOT NULL,
    [entity_key] [int] NOT NULL,
    [ssn] varchar(9) NOT NULL,
)
insert into #Data_table (Name, SSN)
select 'John', '123456789' union all
select 'John', '001100110' union all
select 'Jill', '987654321' 



merge into #entity_key_table t
using #data_table s
    on 1 != 1
when not matched then insert
(
    name   
)
values
(
    s.name
)
output inserted.entity_key, s.ssn
into #entity_identifier_table
(
    entity_key,
    ssn
);

select top 1000 *
from #data_table

select top 1000 *
from #entity_key_table

select top 1000 *
from #entity_identifier_table

Answer 2

The problem with your code is that you output data only from inserted or deleted.

Assuming your name column only relates to one SSN, the following would work:

DECLARE @output TABLE (entity_key INT,ssn VARCHAR (11))

INSERT INTO entity (entity_key, name)
OUTPUT [INSERTED].[entity_key], [inserted].[name]
INTO @output 
SELECT D.Entity_key, d.name 
FROM datatable

INSERT INTO entity_identifier (entity_key, ssn)
Select o.entity_key, d.snn
from @output o
join datatable d on o.name = d.name

However, the problem of multiple duplicated names having different Social Security Numbers is extremely high. In this case, your current structure simply does not work because there is no way to know which identity belongs to which name record. (The Merge solution in another post may also have this problem, before you put that to production be sure to test the scenario of duplicated names. The chances of duplicated names in a set of records is extremely high in any reasonable large data set of names and this should be one of your unit test for any potential solution.)

Here is a potential workaround. First, insert the SSN as the name in the first insert, then return output as shown but join on the @output Name column to the SSN column. After doing the other insert, then update the name in the orginal table to the correct name again joining on the SSN data.

DECLARE @output TABLE (entity_key INT,ssn VARCHAR (11))

INSERT INTO entity (entity_key, name)
OUTPUT [INSERTED].[entity_key], [inserted].[ssn]
INTO @output 
SELECT D.Entity_key, d.name 
FROM datatable

INSERT INTO entity_identifier (entity_key, ssn)
Select o.entity_key, d.output
from @output o


update e
set name = d.name
FROM entity e
join @output o on e.entity_key = o.entity_key
join datatable d on o.name = d.ssn