Rebuild auto-numbering sequence

[George P]

I have a table with an auto-number field that somehow has duplicate values.  What is the best way to reset those values to eliminate current duplicates and prevent future duplicates?

[Giles Lewis · Accepted solution]

Here is a script to renumber all the records in a table.

var nm = new NumberManager(tablename);
var gr = new GlideRecord(tablename);
gr.orderBy('sys_created_on');
gr.query();
while (gr.next()) {
	gr.number = nm.getNextObjNumberPadded();
	gr.update();
}

Duplicates typically appear because records are created in a non-production instance, and then get copied (via Update Sets or other means) to production. The best way to prevent duplicates is to never copy records in this table from non-production to production. If, for any reason, you need to copy records created in non-production to production, then you will need to reset the sequence numbers.

[Allen Andreas]

Hello,

The first step is to track down why you have duplicate records. These can be caused from more than just exporting and importing records via XML. These can also be caused by bad scripts and other means. Once you've isolated the issue, then you can work on renumbering to remove the duplication, then from there, you can enforce uniqueness if it wasn't already to prevent this from happening again. 

Example: https://docs.servicenow.com/en-US/bundle/sandiego-platform-administration/page/administer/field-admi...

Please mark reply as Helpful/Correct, if applicable. Thanks!