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

旧城等待， 2024-02-23 07:14 57阅读 0赞

**目录**

允许自增

允许写入的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。

![5b6942cfebb54236b63f3b10d32dd9a1.png][]

#### 表注意事项 ####

去掉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

#### 存储过程 ####

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

[5b6942cfebb54236b63f3b10d32dd9a1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/23/a78ef7a9cef3474eb187dc81ffbc5153.png