Chapter 7: Adding, Changing, and Deleting Data

Reaching the IDENTITY column’s maximum value

The maximum value that you can insert into an IDENTITY column is 10 precision - 1. If you do not specify a precision for the IDENTITY column, Adaptive Server uses the default precision (18 digits) for numeric columns.

Once an IDENTITY column reaches its maximum value, insert statements return an error that aborts the current transaction. When this happens, use one of the following methods to remedy the problem.

Modify the IDENTITY column’s maximum value

You can alter the maximum value of any IDENTITY column with a modify operation in the alter table command.

alter table my_titles
modify title_id, numeric (10,0)

This operation performs a data copy on a table and rebuilds all the table indexes.

Create a new table with a larger precision

If the table contains IDENTITY columns that are used for referential integrity, you must retain the current numbers for the IDENTITY column values.

Use create table to create a new table that is identical to the old one except with a larger precision value for the IDENTITY column.
Use insert into to copy the data from the old table into to the new one.

Renumber the table’s IDENTITY columns with bcp

If the table does not contain IDENTITY columns used for referential integrity, and if there are gaps in the numbering sequence, you can renumber the IDENTITY column to eliminate gaps, which allows more room for insertions.

To sequentially renumber IDENTITY column values and remove the gaps, use the bcp utility:

From the operating system command line, use bcp to copy out the data. For example:
```
bcp pubs2..mytitles out my_titles_file -N -c
```
The -N instructs bcp not to copy the IDENTITY column values from the table to the host file. The -c instructs bcp to use character mode.
In Adaptive Server, create a new table that is identical to the old table.
From the operating system command line, use bcp to copy the data into the new table:
```
bcp pubs2..mynewtitles in my_titles_file -N -c
```
The -N instructs bcp to have Adaptive Server assign the IDENTITY column values when loading data from the host file. The -c instructs bcp to use character mode.
In Adaptive Server, drop the old table, and use sp_rename to change the new table name to the old table name.

If the IDENTITY column is a primary key for joins, you may need to update the foreign keys in other tables.

By default, when you bulk copy data into a table with an IDENTITY column, bcp assigns each row a temporary IDENTITY column value of 0. As it inserts each row into the table, the server assigns it a unique, sequential IDENTITY column value, beginning with the next available value. To enter an explicit IDENTITY column value for each row, specify the -E (UNIX) or /identity (OpenVMS) flag. See the Utility Guide for more information on bcp options that affect IDENTITY columns.

View this book as PDF