hope everybody is ok.
Well, I need to fix an issue in an important table of our database. The scenario is the following:
We have a table for Customers ("tblCustomer"), it has several columns for all customers details. Last one column is named 'Log' where we save all the updates made to each customer, the issue is that the design of this table was wrong because this field is concatenating all update details in this very same field, so today... We have hundreds of replicated data in this field so when the asp.net page wants to run a query to update all customers of this table the memory hangs (today it takes more than 2 days to make an update).
When I say "Log updates" I am referring to every update the user does to any "tblCustomer" rows.
A solution I am trying to do is:
- Modify the asp.net page that updates the "tblCustomer" table
[list=2]Create a new table called "tblCustomerHistory" so we save a history of the updates in this table. This table would have 2 columns "id_Customer" and "Log"[/list]
[list=3]Modify our asp.net page to save only last update in current "Log" column for the "tblCustomer" table.[/list]
[list=4*]Run a query for existing data in 'log' column in "tblCustomer" to move it to "tblCustomerHistory" table in 'log' column.[/list]
* Point 4 is the one I need help with
To get a query that allow me to take current data in "log" column for "tblCustomer" table ("Image_1.PNG") and separate log updates and insert text creating new rows in "tblCustomerHistory" (Image_2.PNG).
Note: log updates to "tblCustomer" table in "Log" column are separated by "<br>" so asp.net can properly display to the user so if in the new table we can have all records with <br> would be a perfect solution, if not possible, I will understand it.
Thanks a lot.