达梦SQLSERVER迁移实战

1.1迁移前准备
1.1.1创建用户并授权DBA
create user ecology_target identified by “";
create user ecology_init identified by "”;
grant DBA to ecology_target;
grant DBA to ecology_init;
1.1.2解决SQLServer表中空格问题
SELECT ‘[’+name+’]’ AS actual_name,*FROM sys.tables WHERE LEN(name+‘x’)-1 > LEN(name)

1.1.3修复空格问题
DECLARE @sqlstr VARCHAR(MAX)=’’
SELECT @sqlstr += ‘EXEC sys.sp_rename @objname = N’’’+name+’’’, @newname = ‘’’+REPLACE(name,’ ‘,’’)+’’’,@objtype = ‘‘object’’’+CHAR(10) FROM sys.tables WHERE LEN(name+‘x’)-1 > LEN(name)
PRINT @sqlstr
EXEC (@sqlstr)

1.1.4修复后再次检查
SELECT ‘[’+name+’]’ AS actual_name,*FROM sys.tables WHERE LEN(name+‘x’)-1 > LEN(name)

1.1.5预处理触发器创建脚本（ecology_ini）
select replace(replace(lower(trigger_body),’ ‘||origin_name||’ ‘,’ ‘||new_tbname||’ ‘),’"’,’’) as sqlstr from (
select trigger_body,replace(old_tbname,’"’,’’) as old_tbname,old_tbname as origin_name,lower(table_name)as new_tbname from (
select trigger_body,table_name,substr(step1,1,instr(step1,‘foreachrow’)-1) as old_tbname from (
select trigger_body,table_name,replace(substr(replace(replace(lower(trigger_body),’ ‘,’’),char(10),’’),instr(replace(replace(lower(trigger_body),’ ‘,’’),char(10),’’),‘beforeinserton’)+14),‘referencingoldrowas"old"newrowas"new"’,’’) as step1
from dba_triggers where owner = ‘ECOLOGY_INIT’ and triggering_type = ‘BEFORE ROW’ and TRIGGERING_EVENT=‘INSERT’ and UPPER(trigger_body) like ‘%.NEXTVAL%’
)f1
)f2
)f3 where old_tbname != new_tbname;

1.1.6执行查询出来的语句(ecology_ini)

1.2迁移实施
1.2.1创建工程

1.2.2选择SQLServer到DM

1.2.3填写SQLServer数据源信息

1.2.4填写达梦数据源信息

1.2.5选择源模式，并只勾选表

1.2.6配置选择

1.2.7完成配置

1.2.8审核迁移信息，开始迁移

1.2.9数据迁移中

1.3报错处理
1.3.1不能对自增列使用DEFAULT约束

手工修改

1.3.2违反非空约束

SQLSERVER非NULL，达梦兼容ORACLE模式下。迁移报错。
现将达梦兼容模式改为0，后重新使用。迁移正常
1.4创建INIT有TARGET没有的表

select ‘create table ecology_target.’||a.table_name||’ as select *from ecology_init.’||a.table_name||’;’ as sqlstr from (
select table_name from dba_tables where owner = ‘ECOLOGY_INIT’ and table_name not like ‘SREF_CON%’
)a left join
(
select table_name from dba_tables where owner = ‘ECOLOGY_TARGET’
)b on a.table_name = b.table_name
where b.table_name is null;
!