How can I import into a join table in a set based statement?

Question

I am importing data from a flat file into a normalized table structure. I am currently using cursors to do inserts into related tables so I have the primary keys to insert into the join table. Can I do this in a set based way in SQL Server 2008 R2?

I have 3 tables: contacts, phones, and contactPhones. After running the import I'd like there to be 2 contacts in the contact table, 2 in the phones table, and 2 in the contactPhones table. The real import is considerably more complicated, but getting this to work will let me migrate the real import from cursors to a set based solution.

It seems like the merge or output keywords should be able to do what I want but I haven't been able to get the syntax to work.

Here is a code sample trying it with OUTPUT. I got this to almost work, except I couldn't reference import.contactId.

create table import(contactId int  identity, phone varchar(50), name varchar(10))
create table contacts (contactId int identity, name varchar(50))
create table contactPhone (contactId int, phoneId int)
create table Phones (phoneId int identity, number varchar(10))

go
insert into import (phone, name)
    select '1872', 'dave'
    union (select '9110', 'Jordan')

insert into contacts
    select name from import
insert into Phones (number)
    OUTPUT import.contactId, INSERTED.phoneId into contactPhone
    select phone from import

select * from contactPhone

Here is a code sample trying it with merge:

create table import(contactId int  identity, phone varchar(50), name varchar(10))
create table contacts (contactId int identity, name varchar(50))
create table contactPhone (contactId int, phoneId int)
create table Phones (phoneId int identity, number varchar(10))

go
insert into import (phone, name)
    select '1872', 'dave'
    union (select '9110', 'Jordan')

insert into contacts
    select name from import

MERGE phones target
    USING (select import.contactId, import.phone, import.name 
            from import join contacts on import.contactId = contacts.contactId) as source
    ON (target.contactId = source.contactId)
    WHEN MATCHED THEN 
        insert into Phones (number)
            OUTPUT import.contactId, INSERTED.phoneId into contactPhone
            select phone from import
    WHEN NOT MATCHED THEN   
        INSERT (name)
        VALUES (source.Name)
        OUTPUT INSERTED.*;



select * from contactPhone

Answer 1

Use merge on contacts and Phones and store the output in a table variable to be used when you insert into contactPhone.

insert into import (phone, name)
select '1872', 'dave' union all
select '9110', 'Jordan'

declare @ContactIDs table(SourceID int primary key, TargetID int)
declare @PhoneIDs table (SourceID int primary key, TargetID int)

merge contacts as c
using import as i
on 0 = 1
when not matched then
  insert (name) values (i.name)
output i.contactId, inserted.contactId into @ContactIDs;

merge Phones as p
using import as i
on 0 = 1
when not matched then
  insert (number) values (i.phone)
output i.contactId, inserted.phoneId into @PhoneIDs;

insert into contactPhone(contactId, phoneId)
select c.TargetID, p.TargetID
from import as i
  inner join @ContactIDs as c
    on i.contactID = c.SourceID
  inner join @PhoneIDs as p
    on i.contactID = p.SourceID  

Using merge..output to get mapping between source.id and target.id

https://data.stackexchange.com/stackoverflow/qt/122662/