Insert a duplicate in IDENTITY column!!!!!

Let me create a table having Identity column.

CREATE TABLE IDENT (ID INT IDENTITY(1,1), NAME VARCHAR(100))

As above query, I created two columns where ID is one of column name which is having IDENTITY constraint and it will generate 1,2,3,,,,m as records are inserted automatically. Continuing my work, let me insert a value into NAME column.

INSERT INTO IDENT VALUES ('DHINAKARAN')

SELECT * FROM IDENT.

Here we can see that after running the above query, the result is as shown in below snippet.Let me create a table having Identity column.

CREATE TABLE IDENT (ID INT IDENTITY(1,1), NAME VARCHAR(100)).

As above query, I created two columns where ID is one of column name which is having IDENTITY constraint and it will generate 1,2,3,,,,m as records are inserted automatically. Continuing my work, let me insert a value into NAME column.

INSERT INTO IDENT VALUES ('DHINAKARAN')

SELECT * FROM IDENT

Here we can see that after running the above query, the result is as shown in below snippet.

 

Now we know IDENTITY column will not allow to insert the duplicate and if we try to insert a duplicate like say 

INSERT INTO IDENT (ID,NAME) VALUES (1,'DHINAKARAN')

we encounter a error as shown below:

Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

As mentioned here it is asking to turn ON the IDENTITY_INSERT option.

So running the following the script will insert the duplicate. 

SET IDENTITY_INSERT IDENT ON

INSERT INTO IDENT (ID,NAME) VALUES (1,'DHINAKARAN')

GO 

SET IDENTITY_INSERT IDENT OFF

SELECT * FROM IDENT

 

Here setting the IDENTITY_INSERT to ON allows explicit values to be inserted into the identity column of a table.