How to solve the import data problem caused by automatic SQL growth mark

For a data table with an automatic growth identifier, its field value is automatically set by the database, which is very troublesome when importing data.

When we export data, we often think about importing the data of the identified field. What should we do?

There are two methods:

1. Use the database management tool to remove the automatic table growth mark on the interface, export the data, and then change it back on the interface. (This method is troublesome and it is not recommended to set the dynamic data table)

2. Use 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 where the specified table is located.

Schema_name
Name of the schema to which the table belongs.

Table
The name of the table that contains the ID column.

Note
At any time, the IDENTITY_INSERT attribute of only one table in a session can be set to ON. If a table has SET this attribute to ON, SQL Server Returns an error message indicating that SET IDENTITY_INSERT has been SET to ON when a SET IDENTITY_INSERT ON statement is sent to another table, the report shows the table whose attribute is set to ON.

If the inserted value is greater than the current table id value, SQL Server automatically uses the new value as the current ID value.


SET IDENTITY_INSERT is SET during execution or running, rather than during analysis.

Permission
You must have the table or the ALTER permission on the table.

Example
The following example creates a table containing the ID column and describes how to use the SET IDENTITY_INSERT settings to fill gaps in the ID values caused by the DELETE statement.

Copy codeThe Code is as follows:
USE AdventureWorks2012;
GO
-- Create tool table.
Create table dbo. Tool (
Id int identity not null primary key,
Name VARCHAR (40) NOT NULL
)
GO
-- Inserting values into 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;
-- Shoshould 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