Migrate records from one table to another and still retaining sys field values after migration

[santhoshaitha]

Hello,

We have a requirement where we need to migrate all records from one custom table to a new custom table.

We need to retain the values of sys fields like Created, Created by, Updated and Updated by fields of the records in the new table as well after the migration.  

Is this possible? If yes, can someone let me know how to do this?

When I migrated the records using a script with assigning the sys field values as well to the new records but the new records still get the current dates and not the dates of records in old table.

Thanks and Regards,
Santhosh

[santhoshaitha]

Hi Robert and Michael,

Thanks for your reply!

I do not want to migrate the sys_id at all.

I just want to migrate only certain fields from the old table to a new record in new table but with the sys fields having same values in old table (mainly the created and updated dates).

Thanks and Regards,
Santhosh

[edwajs]

Hi Santhosh,

Michael Magarino provided the correct answer, above: use the GlideRecord method autoSysFields(false).

You must use this method, otherwise the server is going to automatically update:

1)   sys_created_by

2)   sys_created_on

3)   sys_mod_count

4)   sys_updated_by

5)   sys_updated_on

You want to ensure you populate all these fields, including sys_mod_count.   Either initialize it to zero, or if you want to track the number of updates in the original record, copy it along with the other values.   The bottom line is sys_mod_count is not supposed to be null.   I have seen this cause erratic behavior with history sets if it is left null.

Oh, and for code like this, instead of using assignment statements, you might be better off using GlideRecord setValue().

For example:

var oldGR = new GlideRecord('<whatever table you are copying>');

oldGR.get('<sys_id of an existing record to copy>');

var newGR = new GlideRecord('u_newcustom_table');

newGR.autoSysFields(false);

newGR.setValue('sys_mod_count',oldGR.getValue('sys_mod_count'));

newGR.setValue('sys_updated_by',oldGR.getValue('sys_updated_by'));

newGR.setValue('sys_updated_on',oldGR.getValue('sys_updated_on'));

newGR.setValue('sys_created_by',oldGR.getValue('sys_created_by'));

newGR.setValue('sys_created_on',oldGR.getValue('sys_created_on'));

Ed Wajs

ServiceNow Technical Support

[santhoshaitha]

Hello Ed Wajs,

Thanks for your response!

I saw few other posts suggesting to use setValue() function instead of using assignment statements ("=") but I could not find why is it a better way to do?

Can you explain the advantage of using setValue() ?

Regards,
Santhosh

[Hari27]

Here is an example "How to copy record from one table to another table"

i have created one temporary incident table and i would like to move incident records to this Temporary table 

------------

var inc_table = new GlideRecord('incident');
inc_table.addQuery('active',true);
inc_table.query();

while ( inc_table.next() )
{
var temp_inc = new GlideRecord('u_temp_incident');
temp_inc.initialize();
temp_inc.number = inc_table.number;
temp_inc.priority = inc_table.priority;
temp_inc.state = inc_table.state;
temp_inc.short_description = inc_table.short_description;
temp_inc.autoSysFields(false);
temp_inc.insert();
}