We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype
and value (varchar(MAX)), and every record in doc can have variable 0-n
valuetypes so there is no schema enforced in any way.
Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc. In short it takes values, have some IF logic on cursor
variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.
About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes (docs is primarly 'read
from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted
via exec sp_xml_preparedocum
View Complete Post