在为SQL TableType插入批量记录时获取SCOPE_IDENTITY值

问题描述

我具有以下表结构，为方便起见，我仅标记单个列

I have following table structure, for convenience purpose I am only marking individual columns

• Table_A （ Id，名称，描述）


• Table_1 （ Id 这是标识列，名称...。）


• 表_2 （ Id 这是标识列， Table_A_Id ， Table_1_Id ）

• Table_A (Id, Name, Desc)
• Table_1 (Id this is identity column, Name....)
• Table_2 (Id this is identity column, Table_A_Id, Table_1_Id)

Table_1和Table_2之间的关系为 1 ... *

The relationship between Table_1 and Table_2 is 1...*

现在我为<$创建了一个表类型c $ c> Table_A 称为 TType_Table_A （仅包含 Id 作为列， C＃应用我发送了多个记录）。我已经实现了所需的批量插入功能。

Now I have created a table type for Table_A called TType_Table_A (which only contains Id as column and from my C# app I send multiple records). I have achieved this bulk insert functionality as desired.

我需要的是将记录从< Table_2 插入到 TType_Table_A 说以下语句，我想捕获 Table_2 Id $ c>对于每个插入的记录

What I need is when I insert records into Table_2 from TType_Table_A say with below statements, I would like to capture the Id of Table_2 for each record inserted

declare @count int = (select count(*) from @TType_Table_A); --a variable declared for TType_Table_A

if(@count > 0)
  begin
    insert into Table_2(Table_A_Id,Table_1_Id)
    SELECT @SomeValue, @SomeValueAsParameter FROM @TType_Table_A;
  end;

现在说如果插入2条记录，我想捕获 Id 对于这2条记录中的每条记录。

Now say if 2 records are inserted, I would like to capture the Id for each of these 2 records.

感谢任何输入/帮助

这就是我知道如何实现的方法，但是我想减少存储过程中应用程序或用户游标对数据库的调用

This is what I know how it can be achieved, but I want to reduce DB calls from my app or user cursor in stored procedure

在Table_1中插入记录并返回Id循环.....通过记录并将记录插入到Table_2中，然后返回ID

Insert record in Table_1 and return back the Id Loop.....through records and insert record in Table_2 and return back the Id

OR

从TableType插入/选择时，在存储过程中使用光标

Use cursor in stored procedure when inserting/selecting from TableType

推荐答案

我认为这是Sql Server？然后，您可以像这样使用OUTPUT子句：

I assume this is Sql Server? Then you can make use of the OUTPUT clause, like so:

declare @NewId table (MyNewId INT)

 insert into Table_2(Table_A_Id,Table_1_Id)
 output INSERTED.MyNewId INTO @TempTable(MyNewID)
 SELECT SomeValue, SomeValueAsParameter FROM @TType_Table_A;

 SELECT * FROM @NewId

这篇关于在为SQL TableType插入批量记录时获取SCOPE_IDENTITY值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！