插入一行并返回其主键 [英] Insert a single row and return its primary key

问题描述

在 SSIS 中，如何使用执行 SQL 任务插入不带参数的单行并取回主键，以便将其设置为用户变量?我的插入查询很简单:

In SSIS, How do I use the execute SQL task to insert a single row with no parameters and get the primary key back so I can set it to user variable? My insert query is simply:

INSERT INTO [AdWords.ImportData] (EndDate) VALUES (null)

推荐答案

好问题，我试了几次才弄明白.声明一个 Int32 类型的 SSIS 变量(除非您需要调整 bigint 或 numeric 的大小).我选择了 tablePk 作为我的.

Good question, took me a few tries to figure it out. Declare an SSIS variable of type Int32 (unless you need sizing for a bigint or numeric). I chose tablePk as mine.

执行 SQL 任务

• 常规标签

结果集:无

SQL

INSERT INTO dbo.ImportData (EndDate) VALUES (NULL);
SELECT ? = SCOPE_IDENTITY()

• 参数映射选项卡

变量名:User::tablePk

Variable Name: User::tablePk

方向:输出

数据类型:长整型

参数名称:0

参数大小:-1

这是最初的解决方案，因为我无法理解如何在 普通 查询中获取占位符 ?.不可能像我上面说的那么简单，除了它.

This was the original solution as I couldn't grok how to get the placeholder ? in a normal query. It couldn't as simple as what I had above, except it was.

唯一的区别是使用的查询

The only difference is the query used

SQL

DECLARE @sql nvarchar(500)
, @paramDef nvarchar(500)

SELECT
    @sql = N'INSERT INTO dbo.ImportData (EndDate) VALUES (NULL);
    SELECT @ident = SCOPE_IDENTITY();'
,   @paramDef = N'@ident int OUTPUT'
EXECUTE sp_executesql @sql, @paramDef, @ident = ? OUTPUT

选项 3

如果您使用的是数据流，我将在如何将 T-SQL 语句中的结果集添加到数据流? 简而言之，您需要在 OLE DB 命令之前向数据流中添加一列.在 OLE DB 命令中，您将该空列映射到存储过程中的 OUTPUT 参数，然后当存储过程触发时，它将用过程中的值替换该列.

Option 3

If you're using a data flow, I outline an approach on How to Add the Result Set from a T-SQL Statement to a Data Flow? In short, you need to add a column into the data flow prior to an OLE DB Command. Within the OLE DB Command, you will map that empty column into a OUTPUT parameter from your stored procedure and then as the stored procedure fires, it will replace the column with the value from the procedure.

这篇关于插入一行并返回其主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！