Insert a column with identity

Page creation date :

SQL Server has an IDENTITY property that automatically sets sequential numbers from 1 for a particular column when you add a record. For example, if you set the IDENTITY in the A column and add three records, the a column a column is automatically set to 1, 2, and 3 in the order in which it was added.

Because the value is set automatically even if insert statement is used, it is not necessary to set the value explicitly, but I think that there is a case where i want to set the value explicitly by using it for a foreign key etc.

As an example, suppose you have a table with the IDENTITY property in the ID column, as shown in the figure.

Insert statement simply inserting a value that includes the ID column for this table is as follows:

insert into [テーブル名] ([ID], [Value]) values (1, 1)

However, i think you get the following error when you try to run it.

Msg 544, Level 16, State 1, Line 1 When IDENTITY_INSERT is set to OFF, you cannot insert an explicit value into the identity column of table 'XXXXX'.

Columns that are originally set with IDENTITY are automatically populated, so you cannot explicitly set the value.

If you really want to set it, you need to set "IDENTITY_INSERT" to "ON" as shown in the error message. Make IDENTITY_INSERT on before inserting, as in the following SQL, and then turn it back to OFF when the INSERT is finished.

set identity_insert [テーブル名] on;
insert into [テーブル名] ([ID], [Value]) values (1, 1)
set identity_insert [テーブル名] off;

-- 確認
select * from [テーブル名];

You can now explicitly set the value and insert it. INSERT-SELECT with tables is also valid.

The column with identity is automatically assigned a value from 1, but if you set the value explicitly as described above, you will be curious from what number is assigned next. In this case, the next value assigned is assigned from the next number of the largest value added to the table, so inserting a new record does not duplicate the values. (However, there are exceptions due to disabilities, etc.)