1 Reply Latest reply on Mar 5, 2013 12:18 PM by Adam Warski

    SET IDENTITY_INSERT ON is not working while using with Hibernate

    Krishan Babbar Newbie



      I have two tables


      table user1

      CREATE TABLE [dbo].[user1](

      [id] [bigint] IDENTITY(100,1) NOT NULL,

      [age] [int] NULL,

      [name] [varchar](1) NULL

      ) ON [PRIMARY]


      table user2

      CREATE TABLE [dbo].[user2](

      [id] [bigint] IDENTITY(100,1) NOT NULL,

      [age] [int] NULL,

      [name] [varchar](1) NULL

      ) ON [PRIMARY]


      I'm trying to copy data from one table(USER1) to other table(USER2) table which got one column set to identity.


      String setIdentityOn="SET IDENTITY_INSERT user2 ON";

      String setIdentityOff="SET IDENTITY_INSERT user2 OFF";

      Query setIdentityOnQuery=sessionFactory.getCurrentSession().createSQLQuery(setIdentityOn);



      Query copyquery = sessionFactory.getCurrentSession().createSQLQuery("INSERT into dbo.user2(id, name, age) " +

             "SELECT id, name, age from dbo.user1");



      Query setIdentityOffQuery=sessionFactory.getCurrentSession().createSQLQuery(setIdentityOff);



      While trying to save data there is exception:

      org.hibernate.exception.ConstraintViolationException: Cannot insert explicit value for identity column in table 'USER2' when IDENTITY_INSERT is set to OFF.


      In above code I am already executing "setIdentityOnQuery" then also its gives same exception.There is no impact of setIdentityOnQuery before "copyquery"



      Interesting thing is that the same code is working fine, if I am using jdbc connectivity. But in hibernate only I am getting this problem.


      Any help really appreciable.