Moving a field from a parent to a child table....

[DrewW]

So I have been working on something and it occurred to me after several updates to a process and a table in the system that the design I had was probably not the best.   In reviewing ways I could improve it I decided that maybe the best way would be to create a child table and move the fields that I added to that table.   This brought up several dilemmas, the first being what do I do about the data that is already in the fields and the second was what is the best/easiest way to move a field from a parent table to a child.   Several processes came to mind, first being export all the data delete the columns, add the columns again to the child table and import the data again.   I was not to keen on this process so I wondered if there was a different way to do it.   So this is what I came up with and wanted to hear what people thought about it.

So what I tried to see if it would work was this.   Queried MySQL for the table information to see what the column data type's were.   Added the new child table.   Change the task type for the records I wanted to be in the child table to the class of the new child table.   Added the column I wanted to move to the child table.   Copied the data for the column to be moved from parent to the child.   Deleted the parent column.   Then I updated the dictionary and language tables.   Then flushed the instance cache and waited a few minutes.

These are the SQL statements I ran.

gs.sql("alter table childtable add column columnToMove mediumtext");

gs.sql("update childtable c inner join parentTable p on c.sys_id = p.sys_id Set c.columnToMove = p.columnToMove");

gs.sql("alter table parentTable drop column columnToMove");

gs.sql("update sys_dictionary set name = 'childtable' where name = 'parentTable' and element = 'columnToMove'");

gs.sql("update sys_documentation set name = 'childtable' where name = 'parentTable' and element = 'columnToMove'");

So this appears to have worked with no ill side affects and I was wondering what people thought?   Am I the only person that has needed to do this kind of thing?   Have I gone off my rocker with this...?

[acretion]

Wow that is good to know Mark, never new you could do that before.

[GoBucks]

@Mark StangerI'm getting a 404 to that link.  Are there other resources available that describe/guide how one could promote/demote fields within a table hierarchy?

Update: Nevermind Mark. I have learned through other community posts that the method utilized in that linked solution was likely the promoteColumn() method and the following KBA notes how that is now exclusion listed.  However, this KBA also outlines some common-sense steps to achieve the promoting of a column to a parent table: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0743116

[Curtis2]

Link is no longer valid!?!

[olafcalderon]

test