INSERT a column with IDENTITY set

SQL Server has an "IDENTITY" property that automatically sets the sequential number from 1 to a specific column when you add a record. For example, if you set IDENTITY in column A and add 3 records, column A is automatically populated with values of 1, 2, and 3 in the order in which you added them.

Even if you use an INSERT statement, the value is set automatically, so there is no need to explicitly set the value, but I think that there are cases where you want to set the value explicitly because it is used for a foreign key etc.

As an example, suppose there is a table with the "IDENTITY" property set in the "ID" column as shown in the figure.

An INSERT statement that inserts a value into this table, including the ID column, would look like this:

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

However, when I try to run it, I think that the following error will appear.

Msg 544, Level 16, State 1, Line 1 When IDENTITY_INSERT is set to OFF, explicit values cannot be inserted into the identity column of table 'XXXXX'.

Columns that are originally set to IDENTITY are assumed to have values set automatically, so you cannot explicitly set the values.

If you really want to set it, you need to set "IDENTITY_INSERT" to "ON" as described in the error message. Turn IDENTITY_INSERT ON before INSERTING, and back to OFF when the insert is complete, as in the following SQL:

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 using tables is also valid.

Columns with IDENTITY are automatically assigned values from 1, but if you explicitly set the values as described above, you may be wondering what number will be assigned next. In this case, the next value assigned is assigned from the number after the highest value added to the table, so there is no duplicate value when a new record is inserted. (However, there are exceptions due to disabilities, etc.)