Avoid duplicate records

Serhii5 · ‎06-10-2020

Hi guys, how is it possible to check before insert if the same record exists? I have script include which fetching data and I would like to modify it to validate if a record exists - update it with new data if no -insert new

Michael Ritchie · ‎06-10-2020

Create two array variables:
1. Lets call it resultsArray - Array variable to store the unique identifier that you can use to query your SN table for existence of a record
2. Lets call it existingRecordsArray - Array variable to store the unique identifier of existing records in your SN table
Loop through your "results" variable adding the unique identifiers to the above created array
Query your SN table using the "resultsArray" list to find records that match that identifier. Loop through the found records and populate the "existingRecordsArray" with the unique identifier
Loop through your "results" variable again but check your "existingRecordsArray" to decide to skip or update versus insert

I hope this make sense. Doing it this way will process records much more efficiently since you aren't doing 10000 GlideRecord queries.

View solution in original post

Pradeep Sharma · ‎06-10-2020

Hello Serhii,

Two options here

1. You can use the Import set to transform data into the target table. With this option, system can handle insert or update operations via the Coalesce field.

2. You can use GlideRecord addQuery to check for some unique value in the target table and insert records only if no records found.

- Pradeep Sharma

Serhii5 · ‎06-10-2020

Thanks for your answer. query in for loop with as.field, result[i].field ?

Pradeep Sharma · ‎06-10-2020

yes, you are right.

Ankur Bawiskar · ‎06-10-2020

Hi,

which is the field on the table which is unique?

use that for addQuery() before insert

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader