SQL Server中IDENTITY_INSERT设置无效，如何显式插入自增主键值？

我正在从MySQL迁移至SQL Server，MySQL里向auto_increment主键列传入显式值可以正常插入，但SQL Server中主键列加了Identity约束后，传显式值就报错。

已尝试的解决方案 #

我用JPA实体管理器执行了以下代码：

entityManager.createNativeQuery("SET IDENTITY_INSERT myTableName ON").executeUpdate();
em.merge(entity);
flush();
entityManager.createNativeQuery("SET IDENTITY_INSERT myTableName OFF").executeUpdate();

但这个方案无效，报错信息如下：

Cannot insert explicit value for identity column in table 'myTable' when IDENTITY_INSERT is set to OFF.

场景说明 #

我的应用有两个相同的schema，需要根据业务逻辑把部分表的数据从schemaA复制到schemaB：

• schemaA的id列JPA配置：

@Id
@Column(name = "my_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long myId;

• schemaB通过orm.xml覆盖配置：

<entity class="ca.eqb.commercial.commons.domain.MyTable">
 <attributes>
  <id name="myId">
    <column name="my_id"/>
  </id>
 </attributes>
</entity>

实体管理器工厂配置：

LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(datasource);
em.setMappingResources(ormXmlfile);
.......

Hibernate日志 #

Hibernate: SET IDENTITY_INSERT myTable ON
Hibernate: select <all columns> from myTable
Hibernate: insert into myTable (all columns) values (?,?,?...)
"org.hibernate.exception.SQLGrammarException: could not execute statement [Cannot insert explicit value for identity column in table 'myTable' when IDENTITY_INSERT is set to OFF.]

请问在SQL Server中，还有其他方法可以向Identity列传入显式值吗？

可行解决方案 #

1. 确保操作在同一事务内 #

SQL Server的IDENTITY_INSERT设置是基于单个数据库连接/事务的，你的代码可能因为事务隔离导致设置失效。把整个操作包在同一个事务中：

entityManager.getTransaction().begin();
try {
    entityManager.createNativeQuery("SET IDENTITY_INSERT myTableName ON").executeUpdate();
    entityManager.merge(entity);
    entityManager.flush();
    entityManager.createNativeQuery("SET IDENTITY_INSERT myTableName OFF").executeUpdate();
    entityManager.getTransaction().commit();
} catch (Exception e) {
    entityManager.getTransaction().rollback();
    throw e;
}

2. 调整Hibernate ID生成策略 #

schemaB不需要自动生成ID，需明确告诉Hibernate直接使用传入的ID值，在orm.xml中添加生成策略配置：

<entity class="ca.eqb.commercial.commons.domain.MyTable">
 <attributes>
  <id name="myId">
    <column name="my_id"/>
    <generated-value strategy="NONE"/>
  </id>
 </attributes>
</entity>

这样Hibernate不会自动生成ID，配合IDENTITY_INSERT设置即可正常插入。

3. 使用原生SQL直接插入 #

如果JPA的merge操作仍有问题，绕过JPA直接执行原生插入，需显式指定所有列（包括ID）：

entityManager.getTransaction().begin();
try {
    entityManager.createNativeQuery("SET IDENTITY_INSERT myTableName ON").executeUpdate();
    String insertSql = "INSERT INTO myTableName (my_id, col1, col2) VALUES (?, ?, ?)";
    entityManager.createNativeQuery(insertSql)
        .setParameter(1, entity.getMyId())
        .setParameter(2, entity.getCol1())
        .setParameter(3, entity.getCol2())
        .executeUpdate();
    entityManager.createNativeQuery("SET IDENTITY_INSERT myTableName OFF").executeUpdate();
    entityManager.getTransaction().commit();
} catch (Exception e) {
    entityManager.getTransaction().rollback();
    throw e;
}

4. 调整表结构（可选） #

如果业务允许，直接去掉schemaB中my_id列的Identity约束，这样插入显式ID不会有任何限制。

内容的提问来源于stack exchange，提问作者Prudvinath