How to import into a connection 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 insert into linked tables, so I have primary keys to insert into the join table. Can I do this based on a set in SQL Server 2008 R2?

I have 3 tables: contacts, phones and contacts. After starting the import, I want there to be 2 contacts in the contact table, 2 in the phone table and 2 in the contactPhones table. Real imports are much more complicated, but this will allow me to transfer real imports from cursors to the installation solution.

It seems that the merge or output keywords should be able to do what I want, but I could not get the syntax to work.

Here is sample code trying to execute it using OUTPUT. I got this almost for work, except that I cannot 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 an example of code that is trying to 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 in contacts and Phones and save the output in a table variable that will be used when pasting 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 the mapping between source.id and target.id

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

Answer 2

You can do this without merging. Add contacts and phones, and then create the contact phone relationships that match 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;