Sql server - get record from UDT

See more:

SQL

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