一、 首先利于DTS将关键数据导入oracle数据库,参照selserver2000DTS转换数据(mysql到oracle).doc
二、 由于DTS迁移到oracle的表名字是小写的,程序调用起来很麻烦,最好是将表改名。以下表名不一定准确,可能会超出长度限制,为了方便分辨才这么写的。
Alter table cdb_members rename to uc_members_bbs;
Alter table uc_members rename to uc_members_caimincun;
Alter table dnt_users rename to uc_members_caiyou;
Insert into uc_members select * from uc_members_bbs;--将uc_members_bbs表的数据插入到uc_members表里
Create table uc_members_caimincun_1 as select * from T2 where 1=2;--创建和T2表结构一样的表
Insert into uc_members_caimincun_1 select t.username from uc_members_caimincun T ,uc_members b where c.username=b.username;--备份uc_members_caimincun表和uc_members表的username重复的数据。
delete from uc_members_caimincun where username in (select t.username from uc_members_caimincun T ,uc_members b where c.username=b.username)--删除uc_members_caimincun表和uc_members表的username重复的数据。
Insert into uc_members select * from uc_members_caimincun;--将uc_members_caimincun表的数据插入到uc_members表中;
Create table uc_members_caiyou _1 as select * from uc_members_caiyou;--将uc_members_caiyou表备份
Create table uc_members_caiyou _2 as select * from uc_members_caiyou where 1=2;--创建和uc_members_caiyou结构相同的表
Insert into uc_members_caiyou _2 select t.username from uc_members_caiyou T ,uc_members b where c.username=b.username 将重复数据备份
delete from uc_members_caiyou where username in (select t.username from uc_members_caiyou T ,uc_members b where c.username=b.username)--删除uc_members_caiyou表和uc_members表的username重复的数据。
Insert into uc_members select * from uc_members_caiyou;--将uc_members_caiyou表的数据插入到uc_members表中;
以上是所有username不重重复的数据,还有一些username重复但是email不重复的很可能不是同一个用户,需要把这些用户的信息处理之后导入到uc_members表中。
delete from uc_members_caimincun _1 where username in(select t.username from uc_members_caimincun _1 t,uc_members tt where t.username=tt.username and t.email=tt.email)删除uc_members_caimincun _1表中username和email都和uc_members相同的数据
delete from uc_members_caiyou _2 where username in(select t.username from uc_members_caiyou _2 t,uc_members tt where t.username=tt.username and t.email=tt.email)删除uc_members_caiyou _2表中username和email都和uc_members相同的数据
delete from uc_members_caiyou_2 where username in(select t.username from uc_members_caiyou_2 t, uc_members_caimincun_1 tt where t.username=tt.username and t.email=tt.email)删除uc_members_caiyou_2表与uc_members_caimincun_1表username和email都相同的数据
insert into uc_members_caimincun_1 select * from uc_members_caiyou_2;将uc_members_caiyou_2中的数据插入到uc_members_caimincun_1中
update t2_1 set username=username||1;将uc_members_caimincun _1表中的username+1
insert into uc_members select * from uc_members_caimincun _1;将uc_members_caimincun _1表中的数据插入到uc_members中。
这样用户整合操作完成。
二、 由于DTS迁移到oracle的表名字是小写的,程序调用起来很麻烦,最好是将表改名。以下表名不一定准确,可能会超出长度限制,为了方便分辨才这么写的。
Alter table cdb_members rename to uc_members_bbs;
Alter table uc_members rename to uc_members_caimincun;
Alter table dnt_users rename to uc_members_caiyou;
Insert into uc_members select * from uc_members_bbs;--将uc_members_bbs表的数据插入到uc_members表里
Create table uc_members_caimincun_1 as select * from T2 where 1=2;--创建和T2表结构一样的表
Insert into uc_members_caimincun_1 select t.username from uc_members_caimincun T ,uc_members b where c.username=b.username;--备份uc_members_caimincun表和uc_members表的username重复的数据。
delete from uc_members_caimincun where username in (select t.username from uc_members_caimincun T ,uc_members b where c.username=b.username)--删除uc_members_caimincun表和uc_members表的username重复的数据。
Insert into uc_members select * from uc_members_caimincun;--将uc_members_caimincun表的数据插入到uc_members表中;
Create table uc_members_caiyou _1 as select * from uc_members_caiyou;--将uc_members_caiyou表备份
Create table uc_members_caiyou _2 as select * from uc_members_caiyou where 1=2;--创建和uc_members_caiyou结构相同的表
Insert into uc_members_caiyou _2 select t.username from uc_members_caiyou T ,uc_members b where c.username=b.username 将重复数据备份
delete from uc_members_caiyou where username in (select t.username from uc_members_caiyou T ,uc_members b where c.username=b.username)--删除uc_members_caiyou表和uc_members表的username重复的数据。
Insert into uc_members select * from uc_members_caiyou;--将uc_members_caiyou表的数据插入到uc_members表中;
以上是所有username不重重复的数据,还有一些username重复但是email不重复的很可能不是同一个用户,需要把这些用户的信息处理之后导入到uc_members表中。
delete from uc_members_caimincun _1 where username in(select t.username from uc_members_caimincun _1 t,uc_members tt where t.username=tt.username and t.email=tt.email)删除uc_members_caimincun _1表中username和email都和uc_members相同的数据
delete from uc_members_caiyou _2 where username in(select t.username from uc_members_caiyou _2 t,uc_members tt where t.username=tt.username and t.email=tt.email)删除uc_members_caiyou _2表中username和email都和uc_members相同的数据
delete from uc_members_caiyou_2 where username in(select t.username from uc_members_caiyou_2 t, uc_members_caimincun_1 tt where t.username=tt.username and t.email=tt.email)删除uc_members_caiyou_2表与uc_members_caimincun_1表username和email都相同的数据
insert into uc_members_caimincun_1 select * from uc_members_caiyou_2;将uc_members_caiyou_2中的数据插入到uc_members_caimincun_1中
update t2_1 set username=username||1;将uc_members_caimincun _1表中的username+1
insert into uc_members select * from uc_members_caimincun _1;将uc_members_caimincun _1表中的数据插入到uc_members中。
这样用户整合操作完成。