USE Northwind
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
DROP TABLE IdentityTable
GO
CREATE TABLE IdentityTable (
TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TheValue NVARCHAR(20) NOT NULL )
GO
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
DROP TABLE IdentityTable
GO
CREATE TABLE IdentityTable (
TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TheValue NVARCHAR(20) NOT NULL )
GO
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (1, 'First Row')
GO
VALUES (1, 'First Row')
GO
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF.
SET IDENTITY_INSERT IdentityTable ON
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')
SET IDENTITY_INSERT IdentityTable OFF
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')
SET IDENTITY_INSERT IdentityTable OFF
SET IDENTITY_INSERT IdentityTable ON
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (10, 'Row Ten')
SET IDENTITY_INSERT IdentityTable OFF
INSERT IdentityTable(TheValue)
VALUES ('Should be 11')
SELECT * FROM IdentityTable
GO
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (10, 'Row Ten')
SET IDENTITY_INSERT IdentityTable OFF
INSERT IdentityTable(TheValue)
VALUES ('Should be 11')
SELECT * FROM IdentityTable
GO
Result
---------------------
1 First Row
10 Row Ten
11 Should be 11
No comments:
Post a Comment