March 30, 2015 at 9:26 pm
In a t-sql 2012 stored procedure, I would like to know if my solution to solve the problem is correct and/or if you have any other suggestion(s) on how I can complete the task I am listing below:
In an existing t-sql 2012 stored procedure, there is a table called 'Atrn' that is truncated every night. The Table 'Atrn' has a column called 'ABS' that is populated with incorrect data.
The goal is to place the correct value into 'ABS' column that is located in the Atrn table while the t-sql 2012 stored procedure is excuting.
**Note: The goal is to fix the problem now since it is a production problem. The entire stored procedure that updates the 'dbo.Atrn' table will be rewritten in the near future.
My plan is to:
1. create a temp table called '#Atrnwork' that will contain the columns called,
Atrnworkid int, and ABSvalue with a double value.
2. The value in the column called Atrnworkid in the '#Atrnwork' table, will obtain its value from the key of the 'Atrn' called atrnid by doing a select into. At the same time, the value for ABSvalue will be obtained by running some sql when the select into occurs?
3. The main table called 'Atrn' will be changed with a update statement that looks something like:
Update Atrn
set ABS = ABSvalue
join Atrn.atrnid = #Atrnwork.Atrnworkid
In all can you tell me what a good solutiion is to solve this problem and/or display some sql on how to solve the problem listed above?
March 30, 2015 at 10:05 pm
I'd check if it possible to make ABS a computed column (persistent, if necessary).
Anyway, threre is no point in loading incorrect data jusy to discard it immediately.
If computed column is not an option try to calculate correct ABS values during the loading process, and populate the table with correct data straight away.
_____________
Code for TallyGenerator
March 31, 2015 at 1:11 am
wendy elizabeth (3/30/2015)
In a t-sql 2012 stored procedure, I would like to know if my solution to solve the problem is correct and/or if you have any other suggestion(s) on how I can complete the task I am listing below:In an existing t-sql 2012 stored procedure, there is a table called 'Atrn' that is truncated every night. The Table 'Atrn' has a column called 'ABS' that is populated with incorrect data.
The goal is to place the correct value into 'ABS' column that is located in the Atrn table while the t-sql 2012 stored procedure is excuting.
**Note: The goal is to fix the problem now since it is a production problem. The entire stored procedure that updates the 'dbo.Atrn' table will be rewritten in the near future.
My plan is to:
1. create a temp table called '#Atrnwork' that will contain the columns called,
Atrnworkid int, and ABSvalue with a double value.
2. The value in the column called Atrnworkid in the '#Atrnwork' table, will obtain its value from the key of the 'Atrn' called atrnid by doing a select into. At the same time, the value for ABSvalue will be obtained by running some sql when the select into occurs?
3. The main table called 'Atrn' will be changed with a update statement that looks something like:
Update Atrn
set ABS = ABSvalue
join Atrn.atrnid = #Atrnwork.Atrnworkid
In all can you tell me what a good solutiion is to solve this problem and/or display some sql on how to solve the problem listed above?
I presume that you are thinking of doing it this way because the proc is complex and you do not want to risk affecting any of the other columns' data. If that's the case, fair enough.
But I don't see the point of the temp table. Why not just update Atrn.ABS directly, after running the dodgy proc?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 31, 2015 at 9:31 am
You are correct that, "I presume that you are thinking of doing it this way because the proc is complex and you do not want to risk affecting any of the other columns' data.
You also mentioned the following: "
But I don't see the point of the temp table. Why not just update Atrn.ABS directly, after running the dodgy proc?" I do not understand what you mean here.
Are you saying that I should create a new stored procedure to run after the 'dodgy proc'?
I am thinking that I should add a new step to the 'dodgy' proc to update the incorrect column value. To calculate the correct value, I need to run a 'long new sql' that has proven to work correctly in ssrs reports.
Thus I thought I would need a new table to obtain this value. If I do not need a new table, can you tell me and/or show me the sql of how I can update the column that needs to be corrected?
March 31, 2015 at 10:54 am
Regarding modifications to the new proc, can I suggest that you create a new proc which fixes column ABS and then simply add a row at the end of your old proc which calls this new proc (exec dbo.NewProc)? Keeps it simple.
I will make the assumption that you already have a SELECT statement ready to go which calculates the correct value of ABS and returns the following result set (with the specific column names/aliases): (AtrnId, ABS). I'll refer to this chunk of SQL as qry1.
Your UPDATE statement would look something like this:
with ABSResults as (qry1)
update Atrn
set ABS = ABSResults.ABS
from dbo.Atrn Atrn
Join ABSResults on Atrn.AtrnId = ABSResults.AtrnId
Where Atrn.ABS <> ABSResults.ABS or Atrn.ABS is null
Please note that I have not tested the above, so please do not run it without testing it 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 31, 2015 at 12:36 pm
Thank you so far since your answers have been very helpful!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply