Delete and recreate identity column and fetch the newly created values in an update statement

  • I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

    The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column

    The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

    Below is the schema of the three tables

    I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

    Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

    This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

    Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

    I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

    Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

    After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.

    But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

    I tried to remove the Null value from the #DetailTable by running the update statement of analysisdetail in a while loop however its not working.

    Can any one help me to solve this? Below is the code that I created.

    DECLARE @categoryid INT = 8

    DECLARE @DetailQuestionID INT = 1380

    /*------- I need the query to run for the below three data.

    Here i'm updating my planids that already exists in my database*/

    DECLARE @planids VARCHAR(MAX) = '2,4,5'

    ---DECLARE @planids VARCHAR(MAX) = '2,4'

    ---DECLARE @planids VARCHAR(MAX) = '1,2,4'

    -- Get Analysis Detail ID for previous functionplanid

    CREATE TABLE #DetailTable (

    Id INT IDENTITY(1, 1)

    ,analysisID INT

    ,PlanID INT

    ,OldPlanID INT

    ,NewPlanID INT

    )

    INSERT INTO #DetailTable (

    analysisID

    ,PlanID

    ,OldPlanID

    ) (

    SELECT analysisID

    ,cfpd.PlanID

    ,cfpd.PlanDetailID FROM [db_RACT].[dbo].[AnalysisDetail] rd INNER JOIN [db_RACT].[dbo].[PlanDetail] cfpd ON rd.PlanDetailID = cfpd.PlanDetailID WHERE cfpd.DetailQuestionId = @DetailQuestionID

    )

    ---- Delete previous functionalplan id

    DELETE

    FROM db_ract.dbo.PlanDetail

    WHERE detailquestionid = @detailquestionid;

    ---- Insert New plandetail id for the category

    CREATE TABLE #InsertedRows (

    Id INT IDENTITY(1, 1)

    ,Newplandetailid INT

    ,PlanID INT

    )

    INSERT INTO db_ract.dbo.plandetail (

    detailquestionid

    ,planid

    )

    OUTPUT inserted.PlanDetailID

    ,inserted.planid

    INTO #InsertedRows

    SELECT @detailquestionid

    ,data

    FROM db_ract.dbo.fndatasplit(@planids, ',');

    --- Get Latest plandetailid

    DECLARE @loop INT

    SET @loop = 1

    DECLARE @NewPlanDetailId AS INT

    DECLARE @FPlanId AS INT

    WHILE (

    @loop <= (

    SELECT Count(*)

    FROM #InsertedRows

    )

    )

    BEGIN

    IF EXISTS (

    SELECT FunctionPlan

    FROM #DetailTable

    )

    BEGIN

    SELECT @FPlanId = PlanID

    FROM #InsertedRows

    WHERE ID = @loop

    SELECT @NewPlanDetailId = newplandetailid

    FROM #InsertedRows

    WHERE ID = @loop

    UPDATE #DetailTable

    SET NewPlanID = @NewPlanDetailId

    WHERE PlanID = @FPlanId

    SET @loop = @loop + 1

    END

    END

    --- Update AnalysisDetail Table with New PlanDetail

    DECLARE @intFlag INT

    SET @intFlag = 1

    DECLARE @AnalysisId INT

    DECLARE @NewPlanID INT

    WHILE (

    @intFlag <= (

    SELECT Count(*)

    FROM #DetailTable

    WHERE NewPlanID IS NOT NULL

    )

    )

    BEGIN

    SELECT @AnalysisId = analysisID

    FROM #DetailTable

    WHERE ID = @intFlag

    SELECT @NewPlanID = NewPlanID

    FROM #DetailTable

    WHERE ID = @intFlag

    UPDATE db_RACT.dbo.AnalysisDetail

    SET PlanDetailID = @NewPlanID

    WHERE analysisID = @AnalysisId

    SET @intFlag = @intFlag + 1

    END

    SELECT *

    FROM #DetailTable

    SELECT *

    FROM #InsertedRows

    SELECT *

    FROM AnalysisDetail

    Function DataSplit

    /****** Object: UserDefinedFunction [dbo].[fnDataSplit] Script Date: 25-07-2015 12:21:17 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fnDataSplit]

    (

    @RowData nvarchar(2000),

    @SplitOn nvarchar(5)

    )

    RETURNS @RtnValue table

    (

    Id int identity(1,1),

    Data nvarchar(100)

    )

    AS

    BEGIN

    Declare @Cnt int

    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)

    Begin

    Insert Into @RtnValue (data)

    Select

    Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

    Set @Cnt = @Cnt + 1

    End

    Insert Into @RtnValue (data)

    Select Data = ltrim(rtrim(@RowData))

    Return

    END

  • Hi,

    I'm sure someone will help you but you'll need to provide more information and correct some errors.

    Could you post the DDL for your 3 tables AND fill them with sample data.

    You declare a variable called @planids at the start but don't use it. Is it supposed to be '@functionalplanids' in this line:

    FROM db_ract.dbo.fndatasplit(@functionalplanids, ','); ?

    You have a comment that says 'Start Updating the categorydetailquestion table' - We don't know anything about that table.

    You then delete from PlanDetailId table - is that an error?

    DELETE FROM db_ract.dbo.PlanDetailID

    You'll also need to provide some DML which actually causes your error so that we can reproduce the problem.

    Once that's done, someone will help with a set-based solution to remove these temp tables and while loops.

  • karthik82.vk (7/25/2015)


    I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

    The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column

    The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

    Below is the schema of the three tables

    I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

    Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

    This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

    Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

    I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

    Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

    After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.

    But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

    I tried to remove the Null value from the #DetailTable by running the update statement of analysisdetail in a while loop however its not working.

    Can any one help me to solve this? Below is the code that I created.

    DECLARE @categoryid INT = 8

    DECLARE @DetailQuestionID INT = 1380

    /*------- I need the query to run for the below three data.

    Here i'm updating my planids that already exists in my database*/

    DECLARE @planids VARCHAR(MAX) = '2,4,5'

    ---DECLARE @planids VARCHAR(MAX) = '2,4'

    ---DECLARE @planids VARCHAR(MAX) = '1,2,4'

    -- Get Analysis Detail ID for previous functionplanid

    CREATE TABLE #DetailTable (

    Id INT IDENTITY(1, 1)

    ,analysisID INT

    ,PlanID INT

    ,OldPlanID INT

    ,NewPlanID INT

    )

    INSERT INTO #DetailTable (

    analysisID

    ,PlanID

    ,OldPlanID

    ) (

    SELECT analysisID

    ,cfpd.PlanID

    ,cfpd.PlanDetailID FROM [db_RACT].[dbo].[AnalysisDetail] rd INNER JOIN [db_RACT].[dbo].[PlanDetail] cfpd ON rd.PlanDetailID = cfpd.PlanDetailID WHERE cfpd.DetailQuestionId = @DetailQuestionID

    )

    ---- Delete previous functionalplan id

    DELETE

    FROM db_ract.dbo.PlanDetail

    WHERE detailquestionid = @detailquestionid;

    ---- Insert New plandetail id for the category

    CREATE TABLE #InsertedRows (

    Id INT IDENTITY(1, 1)

    ,Newplandetailid INT

    ,PlanID INT

    )

    INSERT INTO db_ract.dbo.plandetail (

    detailquestionid

    ,planid

    )

    OUTPUT inserted.PlanDetailID

    ,inserted.planid

    INTO #InsertedRows

    SELECT @detailquestionid

    ,data

    FROM db_ract.dbo.fndatasplit(@planids, ',');

    --- Get Latest plandetailid

    DECLARE @loop INT

    SET @loop = 1

    DECLARE @NewPlanDetailId AS INT

    DECLARE @FPlanId AS INT

    WHILE (

    @loop <= (

    SELECT Count(*)

    FROM #InsertedRows

    )

    )

    BEGIN

    IF EXISTS (

    SELECT FunctionPlan

    FROM #DetailTable

    )

    BEGIN

    SELECT @FPlanId = PlanID

    FROM #InsertedRows

    WHERE ID = @loop

    SELECT @NewPlanDetailId = newplandetailid

    FROM #InsertedRows

    WHERE ID = @loop

    UPDATE #DetailTable

    SET NewPlanID = @NewPlanDetailId

    WHERE PlanID = @FPlanId

    SET @loop = @loop + 1

    END

    END

    --- Update AnalysisDetail Table with New PlanDetail

    DECLARE @intFlag INT

    SET @intFlag = 1

    DECLARE @AnalysisId INT

    DECLARE @NewPlanID INT

    WHILE (

    @intFlag <= (

    SELECT Count(*)

    FROM #DetailTable

    WHERE NewPlanID IS NOT NULL

    )

    )

    BEGIN

    SELECT @AnalysisId = analysisID

    FROM #DetailTable

    WHERE ID = @intFlag

    SELECT @NewPlanID = NewPlanID

    FROM #DetailTable

    WHERE ID = @intFlag

    UPDATE db_RACT.dbo.AnalysisDetail

    SET PlanDetailID = @NewPlanID

    WHERE analysisID = @AnalysisId

    SET @intFlag = @intFlag + 1

    END

    SELECT *

    FROM #DetailTable

    SELECT *

    FROM #InsertedRows

    SELECT *

    FROM AnalysisDetail

    Function DataSplit

    /****** Object: UserDefinedFunction [dbo].[fnDataSplit] Script Date: 25-07-2015 12:21:17 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fnDataSplit]

    (

    @RowData nvarchar(2000),

    @SplitOn nvarchar(5)

    )

    RETURNS @RtnValue table

    (

    Id int identity(1,1),

    Data nvarchar(100)

    )

    AS

    BEGIN

    Declare @Cnt int

    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)

    Begin

    Insert Into @RtnValue (data)

    Select

    Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

    Set @Cnt = @Cnt + 1

    End

    Insert Into @RtnValue (data)

    Select Data = ltrim(rtrim(@RowData))

    Return

    END

    Since you haven't posted table DDL and test data yet (see the first link in my signature below for how to do this), I can't test things. However, I do have some suggestions and a question.

    First, the question. Will newplandetailid ever be NULL?

    I think that you can replace some of those loops.

    For instance, this loop:

    BEGIN

    SELECT @FPlanId = PlanID

    FROM #InsertedRows

    WHERE ID = @loop

    SELECT @NewPlanDetailId = newplandetailid

    FROM #InsertedRows

    WHERE ID = @loop

    UPDATE #DetailTable

    SET NewPlanID = @NewPlanDetailId

    WHERE PlanID = @FPlanId

    SET @loop = @loop + 1

    END

    Can be replaced with:

    UPDATE DT

    SET NewPlanID = IR.newplandetailid

    FROM #DetailTable DT

    JOIN #InsertedRows IR ON DT.PlanID = IR.PlanID

    And this loop:

    SELECT @AnalysisId = analysisID

    FROM #DetailTable

    WHERE ID = @intFlag

    SELECT @NewPlanID = NewPlanID

    FROM #DetailTable

    WHERE ID = @intFlag

    UPDATE db_RACT.dbo.AnalysisDetail

    SET PlanDetailID = @NewPlanID

    WHERE analysisID = @AnalysisId

    Can be replaced with:

    UPDATE AD

    SET PlanDetailID = DT.NewPlanID

    FROM db_RACT.dbo.AnalysisDetail AD

    JOIN #DetailTable DT ON AD.analysisID = DT.analysisID

    Also, the split function that you're using doesn't perform well (it might be well enough for your needs). Check out the "Splitting Delimited Strings" link in my signature below.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 2 (of 2 total)

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