Assume that all we know why we are using IDENTITY column in a table.
It you are inserting any row and getting error:
“Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table ‘productsales’ can only be specified when a column list is used and IDENTITY_INSERT is ON.”
Error message, than you should use set identity_insert on/off command
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’)
, (‘Hammer’)
, (‘Saw’)
, (‘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 an error:
— An explicit value for the identity column in table ‘AdventureWorks2012.dbo.Tool’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
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
As you have seen in example, set identity_insert On command tells SQL Server that you are inserting identity value without wondering what identity no is the next.
But be care that you must write whole column names in your insert script. In other words normal way of inserting is:
Insert into dbo.Tool VALUES (‘Garden shovel’);
/* SQL Server assigns next identity no (let’s say 4) and NULL values for others. */
If you want to insert without caring identity, you must specify the identity column name and the value:
— 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
No responses yet