June 20, 2013 at 8:50 am
Hello All,
Keywords:
Cascading update.
(Not using the build in cascade).
The real world is not consistent with the database.
Option 1: Change the real world.
Option 2: Change the database.
Plan is to use a Merge with a combined output clause.
Step 1
Starting at the top (root) of the tabletree, for all parent tables create a new row for every row to be updated. Use the output clause to keep track of all the new generated identities. Use these identities in for the next level of tables.
Step 2
For all the leaf rows do an update.
Step 3
Starting at the leaf side of the tree delete all old rows.
Step 1/2/3 are used so that constraints are respected.
I have got some time to realise (and optimise) this function. 🙂
I am happy with this assignment. :hehe:
I haven't any specific request or questions and assume at the moment I can solve this.:Whistling:
But:
Remarks, improvement, thoughts, questions and suggestions are very welcome.
Thanks, happy with the assignment,
ben brugman
Remarks
The data has to be changed over a large number of tables.
Hierarchycally there are at least 6 levels of tables. (probably more).
The number of rows involved in the update is always a fraction of the total number of rows. The field which is cascaded is always indexed. The total number of rows in a single update is in the thousands or tenthousands at most over all tables. (This is a very rough estimate).
The changed field is also the field of which the partitioned key is derived.
Enough administration should be kept so that the update can be reversed.
The frequency of this code to run will be rare.
Performance on the code is not very important.
Extra complexity:
Some tables do hold the partitioning key field and not the field it is derived from.
Constraints are often switched off.
Sometimes the constraint goes over super/sub types.
(There is an extra field to indicate the parent table, so rows can have different parent tables)
No locking or blocking of entire tables.
Sometimes the content is hierarchical. (Something like folders containing folders).
June 20, 2013 at 9:16 am
Is there a question here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2013 at 9:18 am
Sean Lange (6/20/2013)
Is there a question here?
Yes there is, but not up there 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 20, 2013 at 9:27 am
ChrisM@Work (6/20/2013)
Sean Lange (6/20/2013)
Is there a question here?Yes there is, but not up there 😉
LOL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2013 at 9:34 am
Sean Lange (6/20/2013)
Is there a question here?
The anwser to that question was there:
ben.brugman (6/20/2013)
I haven't any specific request or questions and assume at the moment I can solve this.:Whistling:
But now I have a question:
For the revert function, what would be a good way to go?
Use the same code for the reverse and generate (again) new identities, or use identity insert en reuse the old identities again ?
(The revert code will be used extremely rarely, if ever.).
thanks,
ben
June 20, 2013 at 9:59 am
ben.brugman (6/20/2013)
Sean Lange (6/20/2013)
Is there a question here?The anwser to that question was there:
ben.brugman (6/20/2013)
I haven't any specific request or questions and assume at the moment I can solve this.:Whistling:
So then there was no question.
But now I have a question:
For the revert function, what would be a good way to go?
Use the same code for the reverse and generate (again) new identities, or use identity insert en reuse the old identities again ?
(The revert code will be used extremely rarely, if ever.).
thanks,
ben
You have us at an extreme disadvantage here. We don't know what you are doing. When you say revert function, what does that mean?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 20, 2013 at 4:31 pm
Sean Lange (6/20/2013)
You have us at an extreme disadvantage here. We don't know what you are doing. When you say revert function, what does that mean?
Sorry that I was not clear on that.
The revert function makes the cascaded update undone. So all the rows which were altered by the cascaded update are update again with but in the opposite direction.
An object:
Has an internal identification field. This is an important field which is used throughout the database, a lot of queries select on this field.
Has an partitioningkey field derived from the identification field.
There are many tables which have one or two of the above fields.
These tables often have constraints involving the identification field and an identity.
A real world object should only have a single identification field.
Sometimes an object is registered twice.
The updating code I am writing should resolve this to a single identification field.
The cascaded update should take care of that.
But this update has to be reversible. Meaning that all updates made by the cascaded updata have to be undone. The revert function.
Option 1.
Also reverting all identities to their previous values. This makes a more complete reverse.
Option 2.
Is using the same code for the cascaded delete as for the revert. Although this changes the identity fields, code wil be better tested and if the cascaded update works, there is less room for errors in the revert.
My first choice was option 1 because is does a more correct 'revert', but now I am leaning towards option 2 and accepting that the identities will change in the process.
I assume that other people have solved similar problems, so I hope to learn from their experiences.
I hope this explains it a bit.
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply