April 28, 2019 at 12:38 am
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.
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
April 28, 2019 at 6:23 am
Would you please post the DDL for the user defined table?
April 28, 2019 at 6:31 am
Also, could you post a small sample set of data being passed into the stored procedure that is at least representative of the data actually passed in to the stored procedure?
Is there a one to one relationship between parent and child table (it appears that way based on the code you provided).
April 29, 2019 at 7:17 am
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”.
The Rollback does not 'Rollback' the identity value. Once a identity value is 'given out', then it is not taken back into the 'usable' identity numbers.
When Using the identity mechanism to generate the numbers, this can happen because of a number of reasons. Your process is using the numbers, other processes are using the numbers and on a 'restore' some numbers can be 'missing'.
You 'could' take control in a number of ways. (Never elegant and always be carefull, because of a number of problems). Control the generation of numbers yourself. And when using them for an identity field 'force feed' those numbers. **) Or not using an identity field.
Generation of these numbers can be done using the identity mechanisme or using a 'self' designed mechanism, both come with problems. (Concurrency, hotspots (causing locks), other software which has a 'different' oppinion how to handle these numbers.).
A valid question might be: Why do you want the identity numbers to be consecutive? Or even 'specific' from a certain value?
Ben
**)
SET IDENTITY_INSERT <table_name> ON
....
SET IDENTITY_INSERT <table_name> OFF
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply