源库是MYSQL,如果源库迁移到DM时,源端数据库没有停止业务系统,往往会有数据写入,此时将表结构的自增列迁移到DM以后,种子的值写入DM后,后续可能存在自增列数据冲突的问题,因此需要在DM手动将种子的基础值增加一定数量【一般还是建议将源端数据库停止再写入】。
drop table if exists test_table_1;
drop table if exists test_table_2;
drop table if exists test_table_3;
drop table if exists test_table_4;
create table test_table_1(tabid1 int identity(3,2) primary key, name varchar(32));
create table test_table_2(tabid2 int identity(30,3) primary key, name varchar(32));
create table test_table_3(tabid3 int primary key, name varchar(32));
create table test_table_4(tabid4 int identity(300,3) primary key, name varchar(32));
declare
v_seed int;
v_incr int;
v_select_sql varchar2(800);
v_drop_sql varchar2(800);
v_create_sql varchar2(800);
begin
for temp_row in
(
select
b.table_name ,
a.name col_name ,
ident_seed(b.table_name) temp_seed,
ident_incr(b.table_name) temp_inc
from
sys.syscolumns a,
user_tables b ,
user_objects c
where
a.info2 & 0x01 = 0x01
and a.id =c.object_id
and c.object_name = b.table_name
)
loop
v_drop_sql:='alter table test_table_name drop identity';
v_drop_sql:=replace(v_drop_sql, 'test_table_name', temp_row.table_name);
print v_drop_sql;
execute immediate v_drop_sql;
v_create_sql:= q'*alter table test_table_name add column test_column_name identity(test_seed_id,test_inc_value)*';
v_create_sql:=replace(v_create_sql, 'test_table_name', temp_row.table_name);
v_create_sql:=replace(v_create_sql, 'test_column_name', temp_row.col_name);
v_create_sql:=replace(v_create_sql, 'test_seed_id', temp_row.temp_seed+10000);
v_create_sql:=replace(v_create_sql, 'test_inc_value', temp_row.temp_inc);
print v_create_sql;
execute immediate v_create_sql;
print '处理完一个';
end loop;
end;
/