Creating Child table using already existing data

[tahnalos]

My current client has been making modifications to the Location Table for several years now, adding various fields to the table.  They use several types of location entries, each requiring different fields for each type.  This was pointed out to them as a potential maintenance issue but was ignored.

During an upgrade to Jakarta, there were several issues with the data on the location table.  ServiceNow engineers have identifed the problems as due to updates to the Locations table itself and impacting the data on these fields.  They have recommended that child tables be extended from the Location Tables and the fields added moved to each child "type".

Is there a quick and effective way to "move" certain fields to the child table?  Is there a quick and effective way to "move" data to the child table?  Barring that, if we need to create the new fields and retire the old ones on the original table, is there any way to determine which fields are being used or not?  The client added about 200 new fields on the location form, and most of them on first glance appear to be empty with no data.

Please help.

Thanks

[Stewe Lundin]

I'd go for the simple solution. 

Create the new extended tables. 

Export the data from the parent table.
Delete the exported data from the parent table.
Import them in to the corresponding extended tables. 
What that will do to the sys_id's I don't know. 
But test it out in a dev instance. 

[tahnalos]

Is there any way to check which columns have data?  We are looking at potentially retiring unused columns when we move the data to the child table.

Also, is there any way to transfer column names to the new extended tables?  I don't want to create new column names unless I have to.

[Stewe Lundin]

When you extend a table ALL of the columns inte the parent table is going to be created in the extended child table as well, in your case I think its more a case of retiring  fields rather than creating new ones.

regarding the finding empty columns, to my knowledge you need to do this manually, or write a script for it...
But nothing automatic. This is not SOP so to speak. Usally you plan this a head and take care of it before you end up in a mess. 

If I was in this situation I'd do the following. 

1 analyse the cata in the core table. 

2 classify it to a number of desired classes.  

3 extend the different classes and clean up the class tables som that only have fields that are relevant to the desired class. 

4 start moving data. 


I have never done this my self, so  there is a few lose variables here:
*dont know if you can delete columns in a Parent table that are extended to a child table and vise versa.

But I dont think that you have any tools for this rather than it beeing all manual. 

There are probarbly different solutions and approaches to this but this is haw I would attack it. 

Good luck 

[tahnalos]

How would you handle the variable names then?

The major problem that I have is an integration is using the current location table variable names and in order to move them to the extended table, I would have to create all new names for each variable, and that would be a very time consuming task, not to mention a lot of paperwork to track all the variable changes so that it can be ported to the integration

Moving the table reference in the integration is not a problem, it's the column names that give me the fits and especially considering that there are over 200 of them, that is going to be a really messy task.