sql-server - 如何导入基于集合的语句中的联接表？

Question

我正在将数据从平面文件导入到规范化的表结构中。我目前正在使用游标对相关表进行插入，因此我有主键可以插入到连接表中。我可以在 SQL Server 2008 R2 中以基于集合的方式执行此操作吗？

我有 3 个表:联系人、电话和联系人电话。运行导入后，我希望联系人表中有 2 个联系人，电话表中有 2 个联系人，contactPhones 表中有 2 个联系人。真正的导入要复杂得多，但是让它发挥作用将使我将真正的导入从游标迁移到基于集合的解决方案。

看起来合并或输出关键字应该能够执行我想要的操作，但我无法使语法正常工作。

这是一个使用 OUTPUT 进行尝试的代码示例。我几乎可以正常工作，只是我无法引用 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

这是一个尝试合并的代码示例:

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

对联系人和电话使用merge，并将输出存储在表变量中，以便在插入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/