AX1775

Importing data into tables with identity columns

Columns that use the Identity data type contain automatically-generated, unique ID numbers. If you want to import data into a table with an identity column, special considerations apply. The identity column can be mapped as part of the import, or left unmapped. The decision of how to handle this depends on whether you are updating existing data or only creating new records, and whether you want new records to use automatically generated numbers or specific numbers.

Updating existing data with an identity key column

If the import is updating existing data and the table contains an identity key column, the identity column must be mapped. In order to update existing data, the import must be able to match the value in the import temp table with an identity key in the destination table.

When mapping a temp table column to an Identity column in the destination table, the data type of the temp table column must be set to Integer. (If the identity column is Identity32, the temp table column should be set to Integer32.)

Creating new records with an identity key column

If the import is creating new records in the destination table, you can opt to use automatically-generated ID numbers in the identity column or you can specify the ID numbers.

If you want to use automatically-generated ID numbers, there are two ways to accomplish this:

  • Leave the identity column unmapped. When the identity column is unmapped, all records in the import data are created as new records with automatically-generated ID values. This configuration is appropriate when the only purpose of the import is to create new records. If the import data contains a mix of new and updated records, then the identity column cannot be left unmapped.

    This is also the only use case where it is possible to leave a key column unmapped in an import.

  • Map the identity column, but leave the column blank for new records. When the identity column is mapped, but the temp table column is left blank, new records are created for the blank values using automatically-generated ID values. This configuration is appropriate when the import data contains a mix of new and updated records. If the column contains an existing identity value, the existing record is updated.

In some cases, you may have a need to create new records using specific identity values. You can do this by mapping the identity column, and populating the import data with the desired identity values. When new records are created in the table, they will use the specified values instead of using automatically-generated values. When using this approach, keep in mind the following:

  • In order to create new records, the values in the temp table column must not already exist in the destination table. If a temp table value matches an existing value, then the existing record is updated instead of creating a new record.

  • The "seed" value for the identity column will be reset to the largest inserted value, instead of continuing where it left off. For example, if the last auto-generated value was 20, but you import a specific new value of 80, the next auto-generated value will start at 81.

  • It is not possible to create new records with a mix of automatically-generated ID values and specific values. If you want to create any new records with specific values, then all new records in the import data must be assigned specific values. The temp table column cannot be left blank when using this configuration.

When mapping a temp table column to an identity column, the data type of the temp table column must be Integer (see previous section).

Other considerations

It is also possible for a table to contain a non-key identity column. This column can be mapped or unmapped for an import, and will be treated as follows:

  • If the identity column is unmapped, then new records are created using automatically-generated values, and existing values are unchanged. This is the recommended approach unless you need to create new records with specific identity values (an unlikely situation).

  • If the identity column is mapped, and new records are created in the table, the value in the temp table column will be used if it is available. If the value already exists in the identity column on another record, then the value is ignored and an automatically-generated value is used. When updating existing records, the temp table value is ignored and the existing values are left as is (in other words, it is not possible to change the identity value on an existing record).

    As mentioned previously, if new records are created with specific values, the "seed" value for the identity column is reset to the largest inserted value, instead of continuing where it left off.