Sql server - 从UDT获取记录 [英] Sql server - get record from UDT
问题描述
我正在尝试从User-Defined Table类型中获取数据，该数据需要将值插入Parent&儿童表。在下面存储的过程中，我使用单独的SELECT语句从用户定义表中获取值并插入到Parent& ;;儿童表。你可以帮我解决下面的问题吗？
1）有没有可能使用单个SELECT语句来获取列，而不是使用两个select语句用户定义表中是否需要？
2）我一直在使用Commit&回滚Tran。如果在将记录插入Parent表时发生任何异常，它将转到Catch块，但标识值将被跳过一个。例如。我在用户定义的表中有3条记录，并在处理第二条记录时出错。第1和第3条记录的父表的标识列值应为101& 102，但它插入为101& 103。
3）建议我可以使用任何其他方法将记录插入用户定义表的Parent\Child表中，以获取每个插入的状态是成功还是失败。
我尝试过：
CREATE PROCEDURE [dbo]。[SP_Insert]
@insertTable [dbo]。[UserDefindTable] READONLY
AS
BEGIN
- 获取总记录数
DECLARE @totalRecords int = isnull（（从@insertTable中选择count（1）），0）
- 计数器值for while..lo
DECLARE @counter int = 1
- 从父表中获取标识列值
DECLARE @IdentityColumn as int
- 带有Success\Failure状态的返回表
DECLARE @returnTable TABLE（ID INT标识（1,1），[resultId] varchar（50），isSuccess位）
DECLARE @KeyValue VARCHAR（50）
WHILE（@counter< =（ @totalRecords））
BEGIN
SET @KeyValue =（SELECT TOP 1 [KeyValue] FROM @insertTable其中[row_id] = @counter ）
BEGIN尝试
BEGIN TRAN
- 插入父表
INSERT INTO [ParentTable]（Col2，Col3，Col4，Col5）
（从@insertTable中选择Col2，Col3，Col4，Col5，其中[row_id] = @counter）
SET @IdentityColumn = SCOPE_IDENTITY（）
- 插入子表
INSERT INTO [ChildTable]（Col1，Col6，Col7）
（SELECT @IdentityColumn，KeyValue，Col7 FROM @insertTable，其中[row_id] = @counter）
- 插入结果集表
INSERT INTO @returnTable（[KeyValue]，isSuccess）VALUES（@KeyValue，1）
COMMIT TRAN
结束尝试
BEGIN CATCH
ROLLBACK TRAN
- 插入结果集表
INSERT INTO @returnTable（ [KeyValue]，isSuccess）V ALUES（@KeyValue，0）
END CATCH
SET @counter = @counter + 1
END
SELECT * FROM @returnTable
END
I’m trying to get data from User-Defined Table type which has the values needs to be inserted into Parent & Child table. In the below-stored procedure, I’m using separate SELECT statement for getting values from User Defined Table and inserting into Parent & Child table. Can you please help me with the following questions?
1) Instead of using two select statements, Is there any possibility to use a single SELECT statement for getting the columns which are required from User Defined table?
2) I’ve been using Commit & Rollback Tran. If any exception when inserting record into Parent table it’s going to Catch block, but the identity value is getting skipped by one. For eg. I’ve 3 records in User-defined table and getting an error when processing 2nd record. The identity Column value of Parent table for the 1st and 3rd record should be "101 & 102", but it’s inserting as "101 & 103".
3) Suggest me is any other alternative approach available to insert the record into Parent\Child table from User Defined Table for getting the status of each insertion whether it is Success Or Failures.
What I have tried:
CREATE PROCEDURE [dbo].[SP_Insert]
@insertTable [dbo].[UserDefindTable] READONLY
AS
BEGIN
--Getting Total Record Count
DECLARE @totalRecords int = isnull((select count(1) from @insertTable), 0)
--Counter value for while..loop
DECLARE @counter int = 1
--Getting Identity column value from Parent table
DECLARE @IdentityColumn as int
--Return table with Success\Failure status
DECLARE @returnTable TABLE (ID INT identity(1, 1),[resultId] varchar(50),isSuccess bit)
DECLARE @KeyValue VARCHAR(50)
WHILE (@counter <= (@totalRecords))
BEGIN
SET @KeyValue = (SELECT TOP 1 [KeyValue] FROM @insertTable where [row_id] = @counter)
BEGIN TRY
BEGIN TRAN
--Insert into Parent Table
INSERT INTO [ParentTable] (Col2,Col3,Col4,Col5)
(SELECT Col2,Col3,Col4,Col5 FROM @insertTable where [row_id] = @counter)
SET @IdentityColumn = SCOPE_IDENTITY()
--Insert into Child Table
INSERT INTO [ChildTable] (Col1, Col6, Col7)
(SELECT @IdentityColumn, KeyValue, Col7 FROM @insertTable where [row_id] = @counter)
--Insert into resultset table
INSERT INTO @returnTable ([KeyValue],isSuccess) VALUES (@KeyValue, 1)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
--Insert into resultset table
INSERT INTO @returnTable ([KeyValue],isSuccess) VALUES (@KeyValue, 0)
END CATCH
SET @counter = @counter + 1
END
SELECT * FROM @returnTable
END
推荐答案
我不担心工作的部分（将2选择为1） ）。
https://blog.sqlauthority.com/2014/10/08/sql-server-reset-the-identity-seed-after-rollback-or-error/ [ ^ ]
I wouldn't worry about the parts that are "working" (turning 2 selects into 1).
https://blog.sqlauthority.com/2014/10/08/sql-server-reset-the-identity-seed-after-rollback-or-error/[^]
这篇关于Sql server - 从UDT获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
