how can a program create a staging table for import of many rows?

[Dave Oshinsky]

I'm trying to write a program (as opposed to a human running this manually) that can import to an existing target table. The Import Set API insertMultiple feature seems to be the only efficient way (please correct if wrong) to import many records to a target table. Import Set API requires a staging table, and a transform map to specify how to transform records from the staging table into the target table.

Here's my question: how would such a program (using REST API's in particular) create an empty staging table? The Table REST API has a "create" function, but that means to "create" a row, not to "create" the table.

I've started experimenting with creating tables manually, but see no way to do this from a program (using REST API's). Related to this, some tables (like "Incident") don't appear to be extensible, so manually creating a new table derived from "Incident" (to serve as a staging table for import to "Incident") seems unnecessarily difficult. One can extend Task, but not Incident (which extends Task). Am I missing something?  (Yes, a second question...)

[DrewW]

What is your end goal?

Why are you trying to do this?

If you want to use a web service, why will the default table web services not work for what you are trying to do?

[Amit Gujarathi]

HI @Dave Oshinsky ,
I trust you are doing great.

To accomplish this, you would follow these steps:

1. Use the Table API's "create" function to define a new table based on "Task" or any other appropriate parent table. This will create an empty table with the desired fields and properties.

POST /api/now/table/sys_db_object

{
  "name": "u_staging_table",
  "label": "Staging Table",
  "extends": "task"
}

1. Once the staging table is created, you can configure the transform map to specify how records from the staging table should be transformed into the target table, such as "Incident".

2. You can now use the Import Set API's "insertMultiple" feature to efficiently import multiple records from the staging table to the target table. This API requires the name of the staging table and the transform map to be provided.

[Dave Oshinsky]

Hi Amit,

That sounds like a great way to do this for an extensible table like Task. When I tried to extend Incident, I didn't see it in the list of extensible tables. So, for a non-extensible table, I take it that the complete list of columns would need to be specified when posting to sys_db_object.

[Dave Oshinsky]

Hi @Amit Gujarathi

sys_db_object has name and label fields, but no extends field. So, I don't think what you suggested above will work as is.