For a data table with an automatic growth ID, the value of its field is set automatically by the database; This is cumbersome to guide data.
When we guide the data, we tend to think about the identification of the field data also lead in, how to do?
There are two kinds of methods:
1. Using Database management tools in the interface will be the automatic growth of the table logo removal, guide the data, and then in the interface to change back. (This method trouble, and to move datasheet settings, not recommended)
2. With set IDENTITY_INSERT off and set IDENTITY_INSERT on (this method is recommended)
SET IDENTITY_INSERT [database_name. [Schema_name]. ] Table {on | OFF}
database_name
The name of the database that contains the specified table.
Schema_name
The name of the schema to which the table belongs.
Table
The name of the table that contains the identity column.
Comments
At any time, the Identity_insert property of only one table in a session can be set to ON. If a table has this property set to ON, when a set IDENTITY_INSERT ON statement is issued on another table, SQL Server returns an error message stating that set IDENTITY_INSERT is set to on and reports that the table whose property is set to On is reported 。
If the insertion value is greater than the current identity value of the table, SQL Server automatically uses the new inserted value as the current identity value.
Set IDENTITY_INSERT settings are set at execution or run time, not at parse time.
Permissions
The user must own the table, or have ALTER permission on the table.
Example
The following example creates a table that contains an identity column and explains how to use the SET IDENTITY_INSERT setting to populate the gaps in the identity values caused by the DELETE statement.
Copy Code code as follows:
Use AdventureWorks2012;
Go
--Create tool table.
CREATE TABLE dbo. Tool (
ID INT IDENTITY not NULL PRIMARY KEY,
Name VARCHAR not NULL
)
Go
--inserting values into the Products table.
INSERT into dbo. Tool (Name) VALUES (' screwdriver ')
INSERT into dbo. Tool (Name) VALUES (' Hammer ')
INSERT into dbo. Tool (Name) VALUES (' Saw ')
INSERT into dbo. Tool (Name) VALUES (' shovel ')
Go
--Create a gap in the identity values.
DELETE dbo. Tool
WHERE Name = ' Saw '
Go
SELECT *
FROM dbo. Tool
Go
--Try to insert an explicit ID value of 3;
--should return a warning.
INSERT into dbo. Tool (ID, Name) VALUES (3, ' Garden shovel ')
Go
--SET identity_insert to ON.
SET IDENTITY_INSERT dbo. Tool on
Go
--Try to insert an explicit ID value of 3.
INSERT into dbo. Tool (ID, Name) VALUES (3, ' Garden shovel ')
Go
SELECT *
FROM dbo. Tool
Go
--Drop Products table.
DROP TABLE dbo. Tool
Go
