Copying records from one table to another

[MrBun256]

I created a Custom Table by using ‘Extends table’, which references another table. Basically, I copied everything from that table and will later add Dictionary Entries to reference data from other tables for this Custom Table.

When using the REST API Explorer to test out the response, nothing returns. I realized that extending the table does not automatically copy records from the parent table. How would I go about this to copy the dynamic records from the parent table? Perhaps a Business Rule?

_______________________________________________________

EDIT: 

Thank you for the responses. I want to have all records from (table A) copied to (table B). I also want to copy all records from another table (table C) to (table B) as well. The records in both (table A) and (table C) are dynamic, they are updated queues. Basically, I am combining all data records from (table A) and (table C) to make (table B). I do not want to mess with the data in (table A) or (table C) in any way. This is not a one time migration since the data queues are always updating in (table A) and (table C).

[SanjivMeher]

You can just update the Task Type(sys_class_name) field to the new table, which should migrate data to the new table. Try it on Dev Though before trying on PROD.

[Mwatkins]

There is a field named "sys_class_name" that controls table class membership. In the task table this field has the label, "Task Type" (as Sanjiv mentioned above). However, the field "sys_class_name" exists on all tables and has different labels in some cases, but it normally has the label "Class".

ServiceNow GlideRecord allows you to "switch" the class of a record from one table to another - as long as those tables are in the same table extension hierarchy. So the following code will "migrate" the data from table_a to table_b, automatically migrating all the field values that exist in both places (fields that only exist in the source table would get lost and fields that only exist in the target table would still be empty or have the default values, of course).

var aType = new GlideRecord("table_a");
aType.get("001789e73714200044e0bfc8bcbe5d92");
aType.setValue("sys_class_name", "table_b");
aType.update();

However, you don't want to migrate the data, you want to copy it... meaning an end result where there are two records, one in table A and one in table B. Technically, the 2nd record - the one resulting from the copy operation - will exist in both A and B since B is a child of A, but it sounds like you are ok with that.

So, for that you would need a rule on table A that creates a new record in table B. Note that since Business Rule logic gets inherited by children you would want to have an explicit condition on the Business Rule that verifies sys_class_name=table_a (i.e. Class is "Table A"). Otherwise the Business Rule would run when a record gets inserted into A or any child of A - including B - and you'd get more duplicates than you bargained for. The BR would be something like this:

var tableB = new GlideRecord("table_b");
tableB.initialize();
var copyAttrs = ['field_one', 'field_two', 'field_three'];
for (var i = 0; i < copyAttrs.length; ++i) {
   var field = copyAttrs[i];
   if (tableB.isValidField(field))
      tableB.setValue(field, current.getValue(field));
   else
      this.log.logWarning("Invalid field '" + field + "' provided for table '" + table + "'.");
}

FYI - this code is based on the OutOfBox script include, IncidentUtilsSNC

Please ✅ Correct if this solves your issue and/or  👍 if Helpful

[Mwatkins · Accepted solution]

A third idea occurred to me based on the Out-of-box "Insert and Stay" UI Action. If you call the "insert()" method on a GlideRecord that already exists it will create a copy - no need to manually set all the field values. So, in a Business Rule that runs after/insert on table A, with an explicit condition for "Class" is "Table A", you could do this:

current.setValue("sys_class_name", "table_b");
current.insert();

Provided the tables are in the same hierarchy, this should just copy all the fields right over. In fact, I think this will work for tables that aren't even in the same hierarchy as long as the field names are exactly the same.

Please ✅ Correct if this solves your issue and/or  👍 if Helpful

[MrBun256]

Thank you Mwatkins! That worked.

[Gabriel Quibod]

I tried to apply this method to my own application. My goal is to copy or 'migrate' a record in sc_request table to a custom table of mine but it seems this method produces an error on my end. How should I solve this? This was the error: