I need to move a large number of tags into a new folder structure, but I also need to keep the tag history as well.
I have mapped out the different history tables and how they interact with one another, and have tried to copy a small amount of history over to one renamed tag, however obviously there’s more to it than this this didn’t work. I presume it has something to do with how the created/retired and start/end dates work with the partitions and whatnot.
For a simple case of transferring a single tag’s history over for the last 3 months of data, how would I go about doing this?
Thanks in advance!
It seems that I can simply set the Created datetime field of the tag table (sqlt_te) to be the earliest record’s t_stamp value. The problem I’ve noticed though with the moved tag I’m testing with is that all data copied is quite slow to populate in the trend object (~2-7s depending on range)… When I compare it to the original tag, it’s almost instantaneous.
I used this query (it’s pretty horrid) to copy the old data for last march 2017, where ‘OLD TAG PATH’ and ‘NEW TAG PATH’ are not the real values I used:
INSERT INTO [dbo].[sqlt_data_1_2017_03]
tags.new_id as tagid
FROM [sqlt_data_1_2017_03] data
tagnew.id as new_id
,tagold.id as old_id
,tagnew.tagpath as new_tagpath
,tagold.tagpath as old_tagpath
FROM sqlth_te as tagold
sqlth_te as tagnew ON REPLACE(tagold.tagpath, 'OLD TAG PATH/', 'NEW TAG PATH/') = tagnew.tagpath
WHERE tagnew.tagpath <> tagold.tagpath AND tagold.retired IS NULL AND tagnew.retired IS NULL
) as tags
ON tags.old_id = data.tagid