Post
Ask Question

Identity insert in SQL Server table

"An explicit value for the identity column in table 'VENKAT_TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Usually, we will get this error while inserting the records/data into the identity column.

The reason for this error is,

  "We can't explicitly insert data into the identity. If we want to achieve the same, we should first execute the command Identify insert on the table is ON"

Here is a small code snippet,

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



GO


SP_HELP 
VENKAT_TABLE


GO


SET IDENTITY_INSERT VENKAT_TABLE ON


-- WE CAN INSERT THE IDENTITY VALUES EXPLICITLY


 INSERT INTO VENKAT_TABLE(ID,NAME,AGE) 
VALUES(1,'VENKAT',20)


INSERT INTO VENKAT_TABLE 
VALUES(2,'SANTHOSH',20)


INSERT INTO VENKAT_TABLE VALUES(3,'DEEPAN',20)
 
OOPs, still am getting the same error. The reason is Microsoft SQL Server is insisting us to insert the records with the column specified in the query. Let's sse the actual solution to resolve the same. 

 INSERTINTO VENKAT_TABLE(ID,NAME,AGE) 
VALUES(1,'VENKAT',20)

Cheers,
Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/