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

score 2 · Accepted Answer · edited Jan 18 '21 at 12:31

2

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/

edited Jan 18 '21 at 12:31

Community

1
1

answered Dec 27 '11 at 14:34

Mikael Eriksson

136,425
22
210
281

Mike, this is perfect! I don't understand the 0 = 1 syntax, and never would have figured that out, so I'll have to read up on that later. Thanks again! – David Silva Smith Dec 27 '11 at 15:00
@DavidSilvaSmith - It makes sure that you don't have any matches in the merge because what you really want is to insert all rows. Read more about it in the question I linked. – Mikael Eriksson Dec 27 '11 at 15:02
1

Ohhhhhh I gotcha. Not special syntax, just never match since we are doing an import, and 0 will never equal 1. Thanks! – David Silva Smith Dec 27 '11 at 15:07

score 0 · Answer 2 · answered Dec 27 '11 at 14:42

You could do this without merge. Add the contacts and phones, and then create the contact-phone relations matching the import table:

insert into contacts
    select name from import;

insert into Phones
    select phone from import;

insert into contactPhone
    select  i.contactId
    ,       p.phoneId
    from    import i
    join    phones p
    on      p.number = i.phone
    join    contacts c
    on      c.name = i.name;

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

2 Answers2