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

santhoshaitha · ‎04-28-2017

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

mikemagarino · ‎04-28-2017

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

View solution in original post

balaji_charapal · ‎04-28-2017

Hi Santhosh,

Give a try with Export XML and Import XML, it should work.

Steps:

Source table Go to table in list layout -- > right click --> Export XML.

Target table: Go to table in list layout -- > right click --> Import XML.

santhoshaitha · ‎04-28-2017

Hi Balaji,

Thanks for your reply!

XML export and import is not an option as not all the fields are 1 to 1 mapping and also the column names are different for most of the fields.

Thanks and Regards,
Santhosh

MKM · ‎05-01-2017

I dont think that will work even if all the fields are same. I believe, even if you go to the Target table and do import xml, It will import the data to the same table from where its was exported and not to the table where you are trying to import.

SHA3 · ‎04-28-2017

Hi Santhosh,

Try running back end script and update created and updated field as well.

var test = new GlideRecord('incident');

test.get('d71f7935c0a8016700802b64c67c11c6');

gs.print(test.sys_created_on);

test.sys_created_on = '2016-08-07 09:00:05';

test.update();

gs.print(test.sys_created_on);

This script when run at backend works for the date but adds 07 hours to any value that you add. So you can figure out a way to calculate existing hours to show up.

Regards,

Shilpa