SSIS填充交叉引用表 [英] SSIS populating cross reference table

问题描述

目标表如下:

源表如下:

客户

CustomerId FirstName姓氏电子邮件地址1地址2城市邮政编码

CustomerId FirstName LastName Email Address1 Address2 City Zip

目的地中的人员表是基表(稍后将由新客户表继承).因此，我试图从一个表中导出一行并在目标位置填充3个表.

Person table in destination is a base table (which will later be inherited by new customer table). So I am trying to export a row from one table and populate 3 tables in destination.

我设法通过以下方式做到这一点:

I managed to do this in following way:

1. 从源表(客户)获取记录
2. 创建一个空的AddressId字段
3. 使用OLE DB Command任务填充地址表(它调用存储过程，该过程返回映射到AddressId字段的SCOPE_IDENTITY())
4. 重复步骤3，以填充Person表(并检索PersonId
5. 使用PersonId和AddressId字段填充交叉引用表PersonAddress

此软件包的屏幕截图如下.

Screenshot of this package is below.

此方法的最大问题是OLE DB Command任务正在逐行插入，这会使整个程序包非常慢.使用快速加载是否可以实现相同的目的?

Biggest issue with this approach is that OLE DB Command task is inserting row by row and it makes the whole package extremely slow. Is it possible to achieve the same thing but using fast load?

我能够使用OLE DB Command任务来执行此任务，该任务先调用存储过程，然后

I am able to do it using OLE DB Command task which calls the stored procedure and then

推荐答案

我认为您不需要SSIS. 您可以使用INSERT的OUTPUT子句，该子句将所有身份密钥返回到临时表

I don't think you need SSIS. You can use OUTPUT clause of INSERT which returns all identity keys to a temporary table

让我们尝试重现您的场景...

Lets try reproduce your scenario...

set nocount on

go
create table Customer (CustomerId int, CustomerName varchar(100) null, Address1 varchar(100) null, Address2 varchar(100) )
create table [Person] (PersonId int identity, PersonName varchar(100) null)
create table [Address] (AddressId int identity, AddressLine varchar(100) null)
create table [PersonAddress] (AddressId int, PersonId int )
go

-- create some data...
insert into Customer (CustomerId)   values ( 1000000 +  convert(int,  RAND() * 1000000) )
go 1000 

update Customer
set CustomerName = 'CustomerName ' + convert(varchar, CustomerId),
    Address1 = 'Address1 ' + convert(varchar, CustomerId),
    Address2 = 'Address2 ' + convert(varchar, CustomerId)
go



declare @identities_Person table ([rownumber] int identity,   id int)
declare @identities_Address table ([rownumber] int identity,  id int)

insert into Person (PersonName)
output  inserted.PersonId into @identities_Person
select
    c.CustomerName      
from  Customer c 
order by c.CustomerId

insert into [Address] (AddressLine)
output  inserted.AddressId into @identities_Address
select
    c.Address1
from  Customer c 
order by c.CustomerId

insert into [PersonAddress] (PersonId, AddressId)           
select p.id, a.id
from @identities_Address a
inner join @identities_Person p on p.rownumber = a.rownumber

select *
from PersonAddress pa
inner join [Address] a on a.AddressId = pa.AddressId
inner join [Person] p on p.PersonId = pa.PersonId

这篇关于SSIS填充交叉引用表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！