# Keep tag history when moving tag **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [November 2, 2022, 7:19pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024 "2022-11-02T19:19:21Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![SamuelsenLarsen](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/samuelsenlarsen/32/11901_2.png) [@SamuelsenLarsen](https://forum.inductiveautomation.com/u/SamuelsenLarsen)\ **Post date:** [November 2, 2022, 7:19pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/1 "2022-11-02T19:19:21Z")
Hi, I have to move a local tag provider to a remote tag provider. But I cant figure out how to keep the tag history intact. All help is gratefully received☺️ Stian ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [November 2, 2022, 8:58pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/2 "2022-11-02T20:58:10Z")
The only way I know is to edit the records so that records tied to the old TagId point to the new TagId. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 2, 2022, 9:19pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/3 "2022-11-02T21:19:40Z")
~~In editing the tag ids, I'd be concerned about not retrieving history from these old records since the date range in your new tags will start after the old history was created. I would rather rename the tag path in the table for the old tags. You'll also have to relink them to the new tag provider as well. Definitely something to test in a dev gw first~~ ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [November 2, 2022, 9:25pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/4 "2022-11-02T21:25:01Z")
> [@nminchin](#): > > In editing the tag ids, I'd be concerned about not retrieving history from these old records since the date range in your new tags will start after the old history was created Umm, isn't that a given? It's not a new tag, its the same tag moved to a new location. the old Id will have already been retired (at least it should have been), so telling the system that the old data belongs to the new tag seems like the better approach. IMO ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 2, 2022, 9:39pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/5 "2022-11-02T21:39:25Z")
But the datetimes of the old tag's data will have timestamps before the start date of the new tag Edit: Nevermind, changing `tagid`s in the sqlt\_data tables should be ok. I just wonder if you need to change the created date of the new tag as well or if that's just not looked at when querying for data ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/d/4db031197b3dba6697529c3833fbc54881026211.png) ---
**Author:** ![SamuelsenLarsen](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/samuelsenlarsen/32/11901_2.png) [@SamuelsenLarsen](https://forum.inductiveautomation.com/u/SamuelsenLarsen)\ **Post date:** [November 2, 2022, 10:11pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/6 "2022-11-02T22:11:40Z")
When moving a tag to different folder in the same provider, the old location tag path will have a retired timesamp in \_TE table and the new tag path will have a new id. And for keeping the tag history I only need to edit the old tag path matching the new. But when moving to a new provider, the new tag path in \_TE table haveing a new id, also have a new scid. ---
**Author:** ![SamuelsenLarsen](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/samuelsenlarsen/32/11901_2.png) [@SamuelsenLarsen](https://forum.inductiveautomation.com/u/SamuelsenLarsen)\ **Post date:** [November 2, 2022, 10:16pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/7 "2022-11-02T22:16:53Z")
Do you mean changing all the table names, and scid? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 2, 2022, 10:17pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/8 "2022-11-02T22:17:55Z")
He means just updating the `tagid` fields in the `sqlt_data_...` tables to the `id` of the new tag path in the `sqlth_te` table. The data tables are not tag-provider-aware (or rather, they're made aware of them with the link back to the tag) and hence moving the `tagid` to a new one isn't going to matter if the new tag is in a different tag provider ---
**Author:** ![SamuelsenLarsen](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/samuelsenlarsen/32/11901_2.png) [@SamuelsenLarsen](https://forum.inductiveautomation.com/u/SamuelsenLarsen)\ **Post date:** [November 2, 2022, 10:31pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/9 "2022-11-02T22:31:15Z")
I will do a test tomorrow. Are the queries that Ignition uses for history documented somewhere? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 2, 2022, 10:32pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/10 "2022-11-02T22:32:55Z")
Not that I know of ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [November 3, 2022, 2:24am UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/11 "2022-11-03T02:24:07Z")
One word of caution, if you change the `tagid` values in the `sqlt_data_...` tables, you will lose any related information associated with that tag in regards to scan class info/execution as that is all stored in `sqlth_scinfo` and `sqlth_sce` tables. You may also now have records in your `sqlt_data_...` tables that are older than the execution records in `sqlth_sce` since it is now associated with a new scan class id. > **sqlth\_sce** > A record of executions of scan classes, defined in `sqlth_scinfo` . This table is primarily used to determine when the system was running correctly. The end\_time of an entry is updated, as long as it falls within 2\*rate of the last execution. If not, a new entry is made, and data queried will be "stale" during the gap. I think it's likely that this also impacts tag history calculations, but I'm not certain about that. Personally, instead of changing the `tagid` values in the `sqlt_data_...` tables, I would change the `tagpath` values in the `sqlth_te` table to match the new tagpath and then adjust the `sqlth_scinfo` values (and `sqlth_sce` table if necessary). Depending on whether or not the scan class info still needs to be related to the old provider, it may necessitate duplicating `scinfo` and `sce` records and associate them with the new provider. I know this is a little convoluted. It might not be worth the effort for you, but I wanted to at least call out the pitfalls of changing `tagid` values in the `sqlt_data_...` tables. Edit: I forgot to add, if you are changing the provider, then your `drivid` will change, so if you change tagid values in the `sqlt_data` tables, you will likely end up with partitions holding data that do not match the partitions for the new provider (records in the `sqlth_partitions` table). You have this same problem if changing the tagpath values. It's a headache to properly adjust when moving data between providers... ---
**Author:** ![Cameronin](https://avatars.discourse-cdn.com/v4/letter/c/9fc29f/32.png) [@Cameronin](https://forum.inductiveautomation.com/u/Cameronin)\ **Post date:** [December 20, 2022, 12:52pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/12 "2022-12-20T12:52:28Z")
Hi @lrose I need to move a tag too (same provider though). By "edit the records so that records tied to the old TagId point to the new TagId." do you mean do an update query on the sqlth tables? Which tables would you go with editing? Thanks Cameron ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [December 20, 2022, 11:08pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/13 "2022-12-20T23:08:04Z")
> [@Cameronin](#): > > do you mean do an update query on the sqlth tables Nope, you need to update _all_ `sqlt_data_x` tables and update the `tagid` field. For example, if you move a tag and it changes from id 1400 to id 7300, then you would run this: ```auto UPDATE sqlt_data_1_2022_12 -- do this for all sqlt_data_x tables that you have SET tagid = 7300 WHERE tagid = 1400 ``` Via script, you could use something like this: ```auto tagid_old = 15000000 tagid_new = 16000000 query = ''' SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'sqlt_data%' ''' tables = system.db.runPrepQuery(query) tables = [row['TABLE_NAME'] for row in tables] tx = system.db.beginTransaction() for table in tables: query = ''' UPDATE {} SET tagid = ? WHERE tagid = ? '''.format(table) ret = system.db.runPrepUpdate(query, [tagid_new, tagid_old], tx=tx) print table, 'rows changed:', ret #system.db.commitTransaction(tx) system.db.rollbackTransaction(tx) # comment this out and uncomment the commit above to actually apply the changes system.db.closeTransaction(tx) ``` This will print the number of rows changed for each table (I'm using daily partitions for this test gateway): ```auto sqlt_data_1_20221217 rows changed: 287 sqlt_data_1_20221218 rows changed: 288 sqlt_data_1_20221219 rows changed: 287 sqlt_data_1_20221220 rows changed: 216 ``` **EDIT:** **As discussed below, you also need to update the created date of the affected tags as well in the sqth\_te table** ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [December 20, 2022, 11:58pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/14 "2022-12-20T23:58:00Z")
Keep in mind that if you go about changing tagid's in the `sqlt_data_` tables, you need to make sure you change the `created` value for the new record in `sqlth_te`. In the example above, you would want to change the `created` value for `tagid = 7300` to match the `created` value for `tagid = 1400`. Note that I would still advise against changing tagid values in the `sqlt_data_` tables. If your tag provider **has not changed** , it's much simpler to change the `tagpath` value in `sqlth_te` for the old `tagid` to match the `tagpath` in the new `tagid`. It also preserves the scan class information for that tag in case the new copy of the tag is using a different scan class. I would run the following query based on `tagpath` rather than `tagid` because there is always a high probability that you have multiple rows in `sqlth_te` with the same `tagpath` value (different `created`/`retired` values) and would want to update all of them accordingly (Disclaimer: For others reading this post in the future, please see my post further above regarding the pitfalls with changing providers if you are moving tags between providers.) As always, make sure you have DB backups before trying to do any changes to these tables as you can very quickly get yourself into a state that is not easy to recover from without a backup: ```auto UPDATE sqlth_te old_tag INNER JOIN sqlth_te new_tag ON (new_tag.tagpath = '' AND old_tag.tagpath = '') SET old_tag.tagpath = new_tag.tagpath, old_tag.retired = COALESCE(old_tag.retired, new_tag.created - 1); ``` Not the most elegant query, but it works. **Edit 1** : Changed the query to be `tagpath` based instead of `tagid` and added a note about backing up the DB before trying to make any of these changes. **Edit 2** : Fixed the query. I was rushing when I converted the query to be tagpath based and didn't take into account that some records will already have a `retired` value. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [December 21, 2022, 12:10am UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/15 "2022-12-21T00:10:24Z")
> [@WillMT10](#): > > Keep in mind that if you go about changing tagid's in the `sqlt_data_` tables, you need to make sure you change the `created` value for the new record in `sqlth_te`. In the example above, you would want to change the `created` value for `tagid = 7300` to match the `created` value for `tagid = 1400`. ~~I actually didn't find any issues with just changing the tag ids in the data and not changing the created date of the corresponding tag. This was actually my original concern when @lrose suggested it, but when I tested it, it just worked. It's also happens to be the simplest method to move the history data across~~ These posts: > [@nminchin](#): > > In editing the tag ids, I'd be concerned about not retrieving history from these old records since the date range in your new tags will start after the old history was created. I would rather rename the tag path in the table for the old tags. You'll also have to relink them to the new tag provider as well. Definitely something to test in a dev gw first > [@nminchin](#): > > But the datetimes of the old tag's data will have timestamps before the start date of the new tag > > Edit: > Nevermind, changing `tagid`s in the sqlt\_data tables should be ok. I just wonder if you need to change the created date of the new tag as well or if that's just not looked at when querying for data Edit: Actually I just read one of your later posts RE scan classes which I didn't consider. This is certainly a valid consideration ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [December 21, 2022, 12:35am UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/16 "2022-12-21T00:35:47Z")
If `created` values are not updated, it can break the Easy Chart in Vision. My best guess is that Ignition uses those `created`/`retired` values as a way to cheat and determine which `sqlt_data_` partitions they should be querying for the relevant tag data. If your Easy Chart date selection bridges over the `created` time for the tag, it will show everything up to the start of the relevant partition, but it will not show anything from prior partitions because it assumes, based on the `created` value that there is no data in the older partitions. Here is an example. I have data for this particular tag going all the way back to 2020. All I did was change the `created` value to March 22nd, 2022 - nothing else changed. You can see that this drives the red highlighting in the bottom range, and the data stops at March 1st, 2022 because I have partitions set to monthly and it doesn't try to look at older partitions. ![Screen Shot 2022-12-20 at 7.29.25 PM](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/1/e1517f735375651dda85eb2de816a86da77290bd.png) The only thing different in the screenshot below is that I changed the end date of the range to a time prior to March 22nd, 2022. It shows nothing on the screen even though you can see in the screenshot above there was data going back to at least the 1st of March (in reality, all the way back to 2020): ![Screen Shot 2022-12-20 at 7.33.48 PM](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/2/12dbfef7351d79722f1158e80d2d9c70db46e992.png) ---
**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\ **Post date:** [December 21, 2022, 12:53am UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/17 "2022-12-21T00:53:13Z")
I just ran a test and confirmed that the `created` value appears to impact the `system.tag.queryTag*` functions too... ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [December 21, 2022, 1:00am UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/18 "2022-12-21T01:00:05Z")
Maybe i'm remembering wrong then... :S ---
**Author:** ![Cameronin](https://avatars.discourse-cdn.com/v4/letter/c/9fc29f/32.png) [@Cameronin](https://forum.inductiveautomation.com/u/Cameronin)\ **Post date:** [December 21, 2022, 10:28am UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/19 "2022-12-21T10:28:35Z")
@WillMT10 and @nminchin Thanks humans 😃 That is _a lot_ clearer now for which things to change and why in terms of impact. I am happy! Testing will occur. Cameron ---
**Author:** ![Cameronin](https://avatars.discourse-cdn.com/v4/letter/c/9fc29f/32.png) [@Cameronin](https://forum.inductiveautomation.com/u/Cameronin)\ **Post date:** [December 22, 2022, 1:45pm UTC](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024/20 "2022-12-22T13:45:16Z")
Hello again humans... I'm getting the following error running the query adapted from @WillMT10 ; > Blockquote > GatewayException: SQL error for "UPDATE sqlth\_te old\_tag > INNER JOIN sqlth\_te new\_tag ON > (new\_tag.tagpath = '\' AND > (old\_tag.tagpath = '\' > OR old\_tag.tagpath = '\' > OR old\_tag.tagpath = '\') > ) > SET old\_tag.tagpath = new\_tag.tagpath, old\_tag.retired = COALESCE(old\_tag.retired, new\_tag.created -1) > ": Incorrect syntax near 'old\_tag'. > caused by SQLServerException: Incorrect syntax near 'old\_tag'. Phrased as a SELECT query (without the "SET..." part), it returns what I'd expect, so I think it must be an issue with the SET section. But I don't know what. Halp? Thanks [Next page](https://forum.inductiveautomation.com/t/keep-tag-history-when-moving-tag/66024.md?page=2)