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]

Hello Shilpa,

Thank you for your reply!

Your script is to update the sys date fields of an existing record but not a new one.

But anyway I get what you meant.

Any idea why the time is different when it is queried using a script?

Thanks and Regards,

Santhosh

[mikemagarino · Accepted solution]

Hi Santosh,

You can leverage the GlideRecord method autoSysFields(false) to ensure that the sys_created_on, sys_created_by, sys_updated_on and sys_updated_by fields aren't updated during this process. Then, you can manually copy them right over using a script. Something like this:

var oldRecord = new GlideRecord('old_table');

oldRecord.addQuery('something');

oldRecord.query();

while ( oldRecord.next() ) {

  var newRecord = new GlideRecord('new_table');

  newRecord.initialize();

  newRecord.sys_updated_by = oldRecord.sys_updated_by;

  newRecord.sys_updated_on = oldRecord.sys_updated_on;

  newRecord.sys_created_by = oldRecord.sys_created_by;

  newRecord.sys_created_on = oldRecord.sys_created_on;

  newRecord.custom_field_1 = oldRecord.custom_field_2;

  newRecord.autoSysFields(false);

  newRecord.insert();

}

You may need to use toString() on those fields when copying them over. Test it out both ways and let us know. Something like this:

newRecord.sys_updated_by= oldRecord.sys_updated_by.toString() ;

Cheers,

Mike

[santhoshaitha]

Hello Michael,

Thanks a lot for your help!

This is indeed what I am looking for. I tried the same using a sample data set and was able to migrate the record by retaining the sys date field values.

Thanks and Regards,

Santhosh

[rob_pastore]

If it's even possible, I would advise against it.

1. sys_id's must be unique, so you would need to delete the originals before you 'migrated'

but in reality, you should avoid using sys_ids in your code so there values should not matter.   I would concentrate on whatever needs to be done to make it work without copying the sysids then trying to do it.

[mikemagarino]

I agree with Robert. However, It does not seem like Santhosh wants to copy the sys_id. It looks like he just wants to retain the 4 Updated and Created fields. However, if you want to copy sys_id as well, I too recommend against this. These GUID's should be assigned and handled by the platform and should not be hard coded.

Cheers,

Mike