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

    SET IDENTITY_INSERT ON is not working while using with Hibernate

    babbarkrishan
    babbarkrishan Mar 4, 2013 9:27 AM

      Hi

       

      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);

      setIdentityOnQuery.executeUpdate();

       

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

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

      copyquery.executeUpdate();

       

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

      setIdentityOffQuery.executeUpdate();

       

      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.

       

      Thanks,

      Krishan