Get Records from UDT

  • Whizkid

    Right there with Babe

    Points: 783

    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
  • Lynn Pettis

    SSC Guru

    Points: 442337

    Would you please post the DDL for the user defined table?

  • Lynn Pettis

    SSC Guru

    Points: 442337

    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).

     

  • ben.brugman

    SSChampion

    Points: 13350

    Whizkid wrote:

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply