Inserting multiple rows with a single Import Set API call

Go to solution
Tam_s Holics
Tera Contributor

‎07-05-2019 12:01 PM

Hello Everybody,

I have a question regarding the usage of the Import Set API through REST + JSON web service calls. I can successfully use this to import single records to tables (using import set table -> transform map etc.). 
 
However, when I try to create multiple records using one single Import Set API call with the JSON payload below, the Import Set API creates only one record with the data from the first record in the JSON payload.
 
 
[
{
"u_value": "100",
"u_value_text": "Test 100",
"u_ddic_ref": "T000-MANDT",
"u_dynprofld": "S_SCREEN-MANDT",
"u_short_text": "Client",
"u_comment": "Comment 1",
"u_index": "1",
"u_subscreen_repid": "Z123",
"u_subscreen_dynnr": "100",
"u_sap_technical_details": "SAPT0003518"
},
{
"u_value": "200",
"u_value_text": "Test 200",
"u_ddic_ref": "T000-MANDT",
"u_dynprofld": "S_SCREEN-MANDT",
"u_short_text": "Client",
"u_comment": "Comment 2",
"u_index": "2",
"u_subscreen_repid": "Z123",
"u_subscreen_dynnr": "100",
"u_sap_technical_details": "SAPT0003518"
}
]
 
Do I have to use a different formatting in the JSON maybe? Do I have to set something in the Import Set properties or at the Transformation Map?
 
In some cases I'd need to send hundreds of records to the same table at once through the Import Set API, and obviously I don't want to issue hundreds of API calls.
 
Thanks in advance!
 
Best regards,
Tamás
 
Labels:
  • 14,165 Views
1 ACCEPTED SOLUTION

Go to solution
Oleg
Mega Sage

‎07-06-2019 03:09 AM

Hi Tamás,

short answer on your original question could be: you can't use Import Set API to import more as one record in one REST call. If you examine the documentation of Import Set API (see here) you will see that the POST request is a classical REST call to insert one record to table. Other close questions on the community forum (see here, for example) will confirm the suggestion. Thus if you need to import multiple records and want (or have) to use Import Set API to send JSON data, then you have to make one separate REST call for importing of one record. If you need to import 100 or 1000 records at one it could be slow (or very slow).

In my answer on your question I tried to guess what kind of data you import and to guess whether the choice of Import Set API is really the best choice or at least good choice in your case.

It's important to understand the goal of Import Set, it's advantages and disadvantages. Import Set is good if you need to import from external source some kine of information, which can already exist in ServiceNow. For example, you want to import emails or users from your mail server. Or you want to import or update information about users from Active Directory to ServiceNow. In the case, local users, which already created on ServiceNow should not be deleted, but email information or some other properties should be updated and new users created on Active Directory should be added. For mapping of users one can use some unique property, like User ID (user_name),  Employee Number (employee_number) or some other property, which is the same on both Active Directory and ServiceNow. So if you need detect, whether update or insert operation is required on importing of data or if you need to detect collision of data in multiple sources then Import Set would be very good choice.

On the other side, the example of data, which you included in the text of your question looks like non-unique data. I suppose that you don't want to detect whether close data already exist in some table and to make update of data instead of insert. I suppose that no data collision should be detected on import of the data. I suppose that you just need to insert hundreds of records into some table. If it's so, then the best choice would be to use Scripted REST APIs to create new REST Endpoint, to which you could send all the data (the information about all the hundreds of records) in one REST call. Inside of your JavaScript code, which will be processed on calling of the REST Endpoint you can just use insert method of GlideRecord in the loop to insert every of the record into the destination table. In the way inserting of hundreds of records will be much more quickly as hundreds of REST calls, which insert only one record at once.

Regards
Oleg

View solution in original post

13 REPLIES 13

Go to solution
Oleg
Mega Sage

‎07-06-2019 01:00 PM

You are welcome! You can discuss the situation with your customer. You can measure how long time will take many Import Set API calls. If the final timing will be still acceptable for the customer you can hold Import Set API inspire of all disadvantages. If the performance will be too slow then writing of Scripted REST service will be the solution.

If your question is answered now then you can mark one of my previous posts as "Correct Answer".

Go to solution
amila
ServiceNow Employee
ServiceNow Employee

‎03-30-2021 06:43 PM

This is a feature not supported at that time. But now we have insertMultiple to address this issue.

https://developer.servicenow.com/dev.do#!/reference/api/quebec/rest/c_ImportSetAPI

 

Go to solution
Henckel1
Kilo Contributor

‎12-04-2021 09:23 AM

Hi Tamás
I know you by long have solved this issue.

But I came across your post with the same question and have found that it is possible to use the Import Set for this.

All you need to do is to change the format of the body you have shown above. The array of records you want to sent to ServiceNow needs to be wrapped in an object name "records", as shown below.

And you need to call the endpoint:
https://instancename.service-now.com/api/now/import/importsettablename/insertMultiple

 

 

{

  "records": [

{
"u_value": "100",
"u_value_text": "Test 100",
"u_ddic_ref": "T000-MANDT",
"u_dynprofld": "S_SCREEN-MANDT",
"u_short_text": "Client",
"u_comment": "Comment 1",
"u_index": "1",
"u_subscreen_repid": "Z123",
"u_subscreen_dynnr": "100",
"u_sap_technical_details": "SAPT0003518"
},
{
"u_value": "200",
"u_value_text": "Test 200",
"u_ddic_ref": "T000-MANDT",
"u_dynprofld": "S_SCREEN-MANDT",
"u_short_text": "Client",
"u_comment": "Comment 2",
"u_index": "2",
"u_subscreen_repid": "Z123",
"u_subscreen_dynnr": "100",
"u_sap_technical_details": "SAPT0003518"
}

]

}

You receive a HTTP 200 with response like this: 

{
    "import_set_id": "270423141b988d50b6847805464bcb7a",
    "multi_import_set_id": "af0423141b988d50b6847805464bcb7a"
}

 

Go to solution
Rijeesh Rathna1
Tera Contributor
In response to Henckel1

‎01-16-2022 10:32 PM

Hi,

I have the similar requirement. I have an external source which has the payload information of 2000+ records. I would like to create record in Servicenow for each record in the payload. The sample of my payload is shown below. Can you please help me to achieve this?

[
{
"vm": "vm-123",
"name": "ABCSharePoint",
"power_state": "POWERED_ON",
"cpu_count": 4,
"memory_size_MiB": 24576,
"resource_pool": "resgroup-123",
"pool_name": "ABC-SQL",
"vsphere_instance": "ABC_INFRA",
"total_disk_MiB": 697796,
"timestamp": "2022-01-03T12:23:20.781822+04:00"
},
{
"vm": "vm-12345",
"name": "XYZ-ADC1",
"power_state": "POWERED_OFF",
"cpu_count": 4,
"memory_size_MiB": 16384,
"resource_pool": "resgroup-1234",
"pool_name": "XYZ-DC",
"vsphere_instance": "ABC_INFRA",
"total_disk_MiB": 42949,
"timestamp": "2022-01-03T12:23:20.981633+04:00"
}
]

Thanks,
Rijeesh Rathnakumar

0 Helpfuls

Go to solution
Yonz
Giga Contributor

‎01-31-2022 06:36 AM

Hi all.

I'm trying exactly this (see post above). However, when I try to import, none of the fields get populated. All I get are empty Import Set Records for each record in the import JSON (Exact same format as the example above, but with different data)

Am I missing some steps in creating the Import Set Staging Table, REST API, or something?

 

URL Used:

https://myInstance.service-now.com/api/now/import/x_tkmig_ci_collect_cgx_elements/insertMultiple

 

This is a part of the JSON (Only one record...)

{
"records": [
    {
        "element_id": "100100101",
        "element_name": "ION-7000v-1",
        "tkmx_site_id": "DEU04399",
        "tkmx_site_name": "Azure Data Center",
        "description": "ION Box test 1 in Azure DC",
        "hardware_id": "1234567891",  
        "model_name": "ION-7000",
        "role": "spoke",
        "serial_number": "47114711",
        "software_version": "5.4b",
        "state": "bound" 
    }
]

 

Clicking "Show XML" on on eof the empty records gives....

(The empty fields at the top of the XML are the ImportSet fields I defined)

<xml>
    <x_tkmig_ci_collect_cgx_elements>
        <description/>
        <element_id/>
        <element_name/>
        <hardware_id/>
        <model_name/>
        <role/>
        <serial_number/>
        <software_version/>
        <tkmx_site_id/>
        <tkmx_site_name/>
        <state/>
        <import_set_run/>
        <sys_class_name>x_tkmig_ci_collect_cgx_elements</sys_class_name>
        <sys_created_by>user1</sys_created_by>
        <sys_created_on>2022-01-31 14:08:19</sys_created_on>
        <sys_id>ded5a107db514910016b7d78f49619b1</sys_id>
        <sys_import_row>1</sys_import_row>
        <sys_import_set display_value="ISET0010225">12d5a107db514910016b7d78f49619b1</sys_import_set>
        <sys_import_state>pending</sys_import_state>
        <sys_import_state_comment/>
        <sys_mod_count>0</sys_mod_count>
        <sys_row_error/>
        <sys_target_sys_id/>
        <sys_target_table/>
        <sys_transform_map/>
        <sys_updated_by>user1</sys_updated_by>
        <sys_updated_on>2022-01-31 14:08:19</sys_updated_on>
        <template_import_log/>
    </x_tkmig_ci_collect_cgx_elements>
</xml>

 

0 Helpfuls