Display value of auto-increment insert of database primary key

SQL Server 2008 database primary key auto-increment insert display value

When you delete the table data in the database a few days ago at work, after the deletion, the newly added data does not get the id value of the original data (the table id is the primary key, and set to auto-increment), use SQL Server 2008, and now it has been solved. share with you!

Details:

1. Create the t_test table and set the primary key auto-increment, as shown in figure

2. Insert data into the table

Because the id of the primary key field in the table is auto-incrementing, you do not need to specify display insert when inserting, so the SQL statement is

<span style="font-size:24px;">insert into  [xxx].[dbo].[t_test]  values ('xiaoming')insert into  [xxx].[dbo].[t_test]  values ('hanmei')insert into  [xxx].[dbo].[t_test]  values ('lilei')insert into  [xxx].[dbo].[t_test]  values ('ligang')insert into  [xxx].[dbo].[t_test]  values ('xiaozhi')</span>

The value id field is not specified during the insertion. The database automatically adds the primary key id value. The data in the database is:

3. Delete the data and re-Add the data with the displayed value.

<span style="font-size:24px;">delete [xxx].[dbo].[t_test] where id = 1</span>

Insert data with the displayed value:

<span style="font-size:24px;">insert into  [guagua_new_event_system_test].[dbo].[t_test] values (1,'xiaoming')</span>

Database prompt:

Message 8101, level 16, state 1, 1st rows
Only when the column list is used and IDENTITY_INSERT is ON can an explicit value be specified for the ID column in The 'xxx. dbo. t_test 'table.

When INDENTITY_INSERT is set to ON, the SQL statement is:

<span style="font-size:18px;"> SET  IDENTITY_INSERT [xxx].[dbo].[t_test]  ON </span>

<span style="font-size:18px;"> insert into  [xxx].[dbo].[t_test]  values (1,'xiaoming')</span>

Database prompt:

Message 8101, level 16, state 1, 2nd rows
Only when the column list is used and IDENTITY_INSERT is ON can an explicit value be specified for the ID column in The 'xxx. dbo. t_test 'table.
I have already set INDENTITY_INSERT to ON, but why not add it? I have read the help documentation of SQL Server 2008 to understand the need to create a one-to-one column name when displaying and inserting it.

Therefore, the correct SQL statement is:

<span style="font-size:18px;">SET  IDENTITY_INSERT [xxx].[dbo].[t_test]  ON insert into  [xxx].[dbo].[t_test](id ,name)  values (1,'xiaoming')SET IDENTITY_INSERT [xxx].[dbo].[t_test] OFF</span>

You only need to create a column name when displaying the inserted value, and enable INDENTITY_INSERT that allows the insertion!

--- The road is long, and I will go up and down