Assume that all we know why we are using IDENTITY column in a table.<\/p>\n\n\n\n
It you are inserting any row and getting error:<\/p>\n\n\n\n
\u201cServer: 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<\/em> and IDENTITY_INSERT is ON.\u201d <\/p><\/blockquote>\n\n\n\n<\/p>\n\n\n\n
Error message, than you should use set identity_insert on\/off command<\/p>\n\n\n\n
\n\nUSE AdventureWorks2012; <\/p>
GO <\/p>
— Create tool table. <\/span><\/p>
CREATE TABLE dbo.Tool( <\/p>
ID INT IDENTITY NOT NULL PRIMARY KEY, <\/p>
Name VARCHAR(40) NOT NULL <\/p>
); <\/p>
GO <\/p>
— Inserting values into products table. <\/span><\/p>
INSERT INTO dbo.Tool(Name) <\/p>
VALUES (‘Screwdriver’) <\/p>
, (‘Hammer’) <\/p>
, (‘Saw’) <\/p>
, (‘Shovel’); <\/p>
GO <\/p>
— Create a gap in the identity values. <\/span><\/p>
DELETE dbo.Tool <\/p>
WHERE Name = ‘Saw’; <\/p>
GO <\/p>
SELECT * <\/p>
FROM dbo.Tool; <\/p>
GO <\/p>
— Try to insert an explicit ID value of 3; <\/span><\/p>
— should return an error:<\/span><\/p>
— 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.<\/span><\/p>
INSERT INTO dbo.Tool (ID, Name) VALUES (3, ‘Garden shovel’); <\/p>
GO <\/p>
— SET IDENTITY_INSERT to ON. <\/span><\/p>
SET IDENTITY_INSERT dbo.Tool ON; <\/p>
GO <\/p>
— Try to insert an explicit ID value of 3. <\/span><\/p>
INSERT INTO dbo.Tool (ID, Name) VALUES (3, ‘Garden shovel’); <\/p>
GO <\/p>
SELECT * <\/p>
FROM dbo.Tool; <\/p>
GO <\/p>
— Drop products table. <\/span><\/p>
DROP TABLE dbo.Tool; <\/p>
GO <\/p><\/blockquote>\n<\/div><\/div>\n<\/div><\/div>\n\n\n\n
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.<\/p>\n\n\n\n
\n\nBut be care that you must write whole column names in your insert script. In other words normal way of inserting is:<\/h2>\n<\/div>\n<\/div>\n\n\n\n
Insert into dbo.Tool VALUES (‘Garden shovel’); <\/p>\n\n\n\n
\/* SQL Server assigns next identity no (let\u2019s say 4) and NULL values for others. *\/<\/span><\/p>\n\n\n\n
If you want to insert without caring identity, you must specify the identity column name and the value:<\/p>\n\n\n\n
— SET IDENTITY_INSERT to ON. <\/span><\/p>\n\n\n\n
SET IDENTITY_INSERT dbo.Tool ON; <\/p>\n\n\n\n
GO <\/p>\n\n\n\n
— Try to insert an explicit ID value of 3. <\/span><\/p>\n\n\n\n
INSERT INTO dbo.Tool (ID, Name) VALUES (3, ‘Garden shovel’); <\/p>\n\n\n\n
GO <\/p>\n","protected":false},"excerpt":{"rendered":"
Assume that all we know why we are using IDENTITY column in a table. It you are inserting any row and getting error: \u201cServer: Msg 8101, Level 16, State 1, Line 2An explicit value for the identity column in table ‘productsales’ can only be specified when a column list is used and IDENTITY_INSERT is ON.\u201d Error message, […]<\/p>\n","protected":false},"author":1,"featured_media":845,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,16],"tags":[],"_links":{"self":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/842"}],"collection":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/comments?post=842"}],"version-history":[{"count":4,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/842\/revisions"}],"predecessor-version":[{"id":1001,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/posts\/842\/revisions\/1001"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/media\/845"}],"wp:attachment":[{"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/media?parent=842"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/categories?post=842"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datarebus.com\/index.php\/wp-json\/wp\/v2\/tags?post=842"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}