Datax数据同步支持SqlServer 主键自增与非自增

目录

允许自增

允许写入的SQL

Datax写入插件处理

不允许自增

表注意事项

存储过程

---

SqlServer 在自增处理上跟MySQL这种处理有所区别，对于不通的数据处理场景需求需要区别对待。自增会导致ID来源和目标不一致 而非自增有需要对表进行处理。

允许自增

允许写入的SQL

SET IDENTITY_INSERT table_name ON;

-- 插入数据，指定主键值
INSERT INTO table_name (id, column1, column2, ...)
VALUES (new_id_value, value1, value2, ...);

SET IDENTITY_INSERT table_name OFF;

Datax写入插件处理

核心类：com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter

protected void doBatchInsert(Connection connection, List<Record> buffer)
        throws SQLException {
    PreparedStatement preparedStatement = null;
    Statement statementIdentify = null;
    try {
        statementIdentify = connection.createStatement();
        statementIdentify.execute(String.format("SET IDENTITY_INSERT %s ON", table));

        connection.setAutoCommit(false);
        preparedStatement = connection
                .prepareStatement(this.writeRecordSql);

        for (Record record : buffer) {
            preparedStatement = fillPreparedStatement(
                    preparedStatement, record);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        connection.commit();
    } catch (SQLException e) {
        LOG.warn("回滚此次写入, 采用每次写入一行方式提交. 因为:" + e.getMessage());
        connection.rollback();
        doOneInsert(connection, buffer);
    } catch (Exception e) {
        throw DataXException.asDataXException(
                DBUtilErrorCode.WRITE_DATA_ERROR, e);
    } finally {
        if (null != statementIdentify) {
            statementIdentify.execute(String.format("SET IDENTITY_INSERT %s OFF", table));
            try {
                statementIdentify.close();
            } catch (SQLException unused) {
            }
        }
        DBUtil.closeDBResources(preparedStatement, null);
    }
}

由于Datax json任务内部拆分成了多线程，上述的处理并不能解决自增问题。

不允许自增

在做程序数据搬运时，不能开启自增，需要对表进行处理，去掉标识列。通过sql单独对建表和存储过程进行处理。SqlServer 使用mybatis的ScriptRunner这个执行不是很好，只能手动执行SQL。

表注意事项

去掉IDENTITY(1,0)或 IDENTITY

去掉之前：

CREATE TABLE [dbo].[P_CutType](
	[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Remark] [nvarchar](500) NULL,
	[OrderIndex] [int] NULL,
	[UpdatedDateTime] [datetime] NOT NULL,
	[CreatedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_P_CutType] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[P_CutType] ADD  CONSTRAINT [DF_P_ProductionBatchType_UpdatedDateTime]  DEFAULT (getdate()) FOR [UpdatedDateTime]
GO
ALTER TABLE [dbo].[P_CutType] ADD  CONSTRAINT [DF_P_ProductionBatchType_CreatedDateTime]  DEFAULT (getdate()) FOR [CreatedDateTime]
GO

去掉之后：

CREATE TABLE [dbo].[P_CutType](
	[Id] [int]  NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Remark] [nvarchar](500) NULL,
	[OrderIndex] [int] NULL,
	[UpdatedDateTime] [datetime] NOT NULL,
	[CreatedDateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_P_CutType] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[P_CutType] ADD  CONSTRAINT [DF_P_ProductionBatchType_UpdatedDateTime]  DEFAULT (getdate()) FOR [UpdatedDateTime]
GO
ALTER TABLE [dbo].[P_CutType] ADD  CONSTRAINT [DF_P_ProductionBatchType_CreatedDateTime]  DEFAULT (getdate()) FOR [CreatedDateTime]
GO

存储过程

存储过程没有特别的，直接单独从库表任务导出脚本即可。