July 25, 2015 at 12:54 am
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
July 25, 2015 at 4:52 pm
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.
July 27, 2015 at 10:39 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply