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...?

[jim pisello]

Interesting solution, Drew, and apparently it accomplished what you needed so nice work! I would just caution users who venture into gs.sql land to remember that they are manipulating the basic table structure of their SN instances with very little room for error (by which I mean NONE). There's no opportunity to take back any changes you make, and it is potentially quite easy to brick your entire instance.

I won't comment about whether this was the best/easiest way to get the job done. Each client's needs are different. I will say that I think your previous approach would have been safer. That is, export all of the data from the parent table into a CSV or Excel file, create the new fields on the child table, then import the data. Granted that takes a few more steps, but you're dealing only with the SN UI with all of its fault checking, and the chances of inadvertently causing significant harm to your instance are greatly reduced.

[Mark Stanger]

Hi Drew, this type of issue comes up fairly often.   There are actually built-in utilities to help you manage field promotion/demotion between table hierarchies without impacting table data.   This article should be exactly what you need.

http://www.servicenowguru.com/scripting/promote-field-extended-table-servicenow/

[DrewW]

Well, now that's nice to know...   Thank you for the link.

[Mark Stanger]

No problem.   Please mark your question as answered if this is what you were looking for!