SCOPE_IDENTITY()用于在SSIS中批量插入 [英] SCOPE_IDENTITY() for bulk insert in SSIS

问题描述

在SSIS中，我能够插入行并使用OLE DB命令任务(调用存储过程)来检索行的SCOPE_IDENTITY，但这不是批量插入，加载速度很慢.是否可以在SSIS中使用批量插入来获取插入行的ID?

In SSIS, I am able to make an insert of rows and retrieve their SCOPE_IDENTITY using OLE DB Command Task which calls stored procedure, but this is not bulk insert, it's slow load. Is it possible to get id of inserted rows using bulk insert in SSIS?

示例: 插入客户时，首先必须在人"表中插入记录，然后在客户"表中使用此FK.

Example: When inserting Customer, first I have to insert record in Person table and then use this FK in Customer table.

更新:

这是一个Person和Customer表的结构，需要从外部来源填充.

Here's a structure of Person and Customer tables that need to be populated from external source.

一个选择是在Person表中具有OriginalId，以便在填充Customer表时可以在查找中使用它.但这不能回答我有关SCOPE_IDENTITY和快速加载的问题

One option is to have OriginalId in Person table so that I can use it in lookup when populating Customer table. But that doesn't answer my question about SCOPE_IDENTITY and fast load

推荐答案

我建议您在两个单独的数据流任务中执行插入操作.

I would suggest performing your inserts in two separate Data Flow Tasks.

示例:

数据流1-插入人员表

数据流2-从人员"表中查找FK，然后插入到客户"表中

Data Flow 2 - Lookup FK from Person table and then insert into Customer table

这篇关于SCOPE_IDENTITY()用于在SSIS中批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！