Datax数据同步支持SqlServer 主键自增与非自增
目录
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
存储过程
存储过程没有特别的,直接单独从库表任务导出脚本即可。
纸上得来终觉浅,绝知此事要躬行。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
2019-10-09 windows mysql绿色版配置Mysql5.7.X
2017-10-09 NodeJS在CentOs7下安装