时间戳

首页 > 解决方案 > 基于标识列插入然后更新表的存储过程

sql-server - 基于标识列插入然后更新表的存储过程

问题描述

我有一个用例，我需要为审计表创建 2 个存储过程（插入数据、更新记录）。

第一个 SP 将 12 列数据中的 6 列插入表中，并自动生成一个 ID，我需要将其返回并存储在 Azure 数据工厂的变量中，但由于某种原因，我无法获得该值。我收到一条错误消息，说存储过程没有返回任何值，不确定我缺少什么。

在此之后，下一个管道将在 Azure 数据工厂中运行，一旦运行完成，我需要从数据工厂获取输出值，并使用管道运行后生成的剩余 6 条记录更新同一个表。

下面是我的表架构

CREATE TABLE [gds].[TBL_SF_INTEGRATION_AUDIT](
[SF_Integration_Audit_ID] [int] IDENTITY(5000,1) NOT NULL,
[Integration_Batch_ID] [int] NULL,
[Pipeline_Run_StartDT] [datetime] NULL,
[Source_Schema] [varchar](1000) NOT NULL,
[Source_Object] [varchar](1000) NOT NULL,
[Target_Entity] [varchar](500) NOT NULL,
[Target_Load] [varchar](1000) NOT NULL,
[Source_Rows_Selected] [int] NULL,
[Batch_Count] [int] NULL,
[Successful_Batch_Count] [int] NULL,
[Failed_Batch_Count] [int] NULL,
[Successful_Inserted_Rows] [int] NULL,
[Failed_Rows] [int] NULL,
[Pipeline_Run_EndDT] [datetime] NULL

我创建了一个插入存储过程和一个更新存储过程。我正在使用Scope_Identity()获取标识列的最新值，但我收到一条错误消息，指出存储过程没有返回任何值。

ALTER PROCEDURE [dbo].[SP_Insert_Into_IntegrationAudit]
(
    -- Add the parameters for the stored procedure here 
    @Integration_Batch_ID int,
    @Pipeline_Run_StartDT datetime,
    @Source_Schema varchar(1000),
    @Source_Object varchar(1000), 
    @Target_Entity varchar(500), 
    @Target_Load varchar(1000),
    @SF_Integration_Audit_ID int out
)
AS
BEGIN

    -- Insert statements for procedure here
   INSERT INTO gds.TBL_SF_INTEGRATION_AUDIT
   (Integration_Batch_ID, Pipeline_Run_StartDT, Source_Schema, Source_Object,
   Target_Entity, Target_Load)
   VALUES (@Integration_Batch_ID, @Pipeline_Run_StartDT, @Source_Schema,
           @Source_Object, @Target_Entity, @Target_Load)

    SET @SF_Integration_Audit_ID = SCOPE_IDENTITY()
    RETURN @SF_Integration_Audit_ID 


END

对于更新 SP，我不确定如何从第一个存储过程中传递 ID，以便 SQL 知道应该插入/更新新数据的正确行？我已根据建议更改了 SP 以进行更新。

ALTER PROCEDURE [dbo].[SP_Update_IntegrationAudit]
(
    -- Add the parameters for the stored procedure here
    @Source_Rows_Selected int,
    @Batch_Count int,
    @Successful_Batch_Count int,
    @Failed_Batch_Count int,
    @Successful_Inserted_Rows int, 
    @Failed_Rows int, 
    @Pipeline_Run_EndDT datetime,
    @SF_Integration_Audit_ID int
)

AS
BEGIN

    -- Insert statements for procedure here
   UPDATE gds.TBL_SF_INTEGRATION_AUDIT
   SET Source_Rows_Selected = @Source_Rows_Selected, 
    Batch_Count = @Batch_Count,
    Successful_Batch_Count = @Successful_Batch_Count,
    Failed_Batch_Count = @Failed_Batch_Count,
    Successful_Inserted_Rows = @Successful_Inserted_Rows, 
    Failed_Rows = @Failed_Rows, 
    Pipeline_Run_EndDT = @Pipeline_Run_EndDT
    WHERE SF_Integration_Audit_ID = @SF_Integration_Audit_ID

END
GO

任何帮助都感激不尽！谢谢！！

标签： sql-serverstored-proceduresssmssqlparameterazure-data-factory-pipeline

解决方案

首先，绝对是的，scope_identity是获得Id价值的正确方法。

您说您正在存储Id第一个过程调用的返回值（我假设该列被称为Id您不提供表模式），因此您可以在第二个过程中包含一个参数（@Id int）并将其与您已经提供的其他值 - 您的评论表明您将这样做？

然后在您的第二个程序中，您只需

update gds.TBL_SF_INTEGRATION_AUDIT set
<columns=@values>
where id=@id

捕获 的另一种方法Identity是使用output子句将identity value与其他相关值一起捕获到单独的表中。

您可以执行以下操作

insert into gds.TBL_SF_INTEGRATION_AUDIT (<columns>) 
output inserted.Id, inserted.Integration_Batch_Id into <another table> -- or any other columns needed
values (<values>)

然后，您可以join使用已知值来查找Id生成的值。

推荐阅读