新破天一剑合区SQL（半成品）

--可以合区,但合区后人物的东西消失了  -_-!!

--存在这里，供以后遇到相关需要时翻查。

--[user]的触发器

CREATE TRIGGER update_chr ON dbo.[user] FOR insert AS
BEGIN        
         update [user] set chr2=chr1+1, chr3=chr1+2 where account = (select account from inserted)
END

--准备工作：

--取消db1库user表chr1字段、chr表cp_no字段的标识列
--取消db3库user表chr1字段、chr表cp_no字段的标识列

--删除db1库user表的触发器update_chr
--删除db3库user表的触发器update_chr

--用以下SQL修复db3的chr_data1-3表，记得改数据库名和表名（先选择要操作的库）
declare @dbname varchar(255)
declare @tbname varchar(255)

set @dbname = 'db1'
set @tbname='chr_data1'
use @dbname
exec sp_dboption @dbname, 'single user','true'
dbcc checktable(@tbname, REPAIR_ALLOW_DATA_LOSS)
dbcc checktable(@tbname, REPAIR_REBUILD)
exec sp_dboption @dbname,'single user','false'
set @tbname='chr_data2'
exec sp_dboption @dbname, 'single user','true'
dbcc checktable(@tbname, REPAIR_ALLOW_DATA_LOSS)
dbcc checktable(@tbname, REPAIR_REBUILD)
exec sp_dboption @dbname,'single user','false'
set @tbname='chr_data3'
exec sp_dboption @dbname, 'single user','true'
dbcc checktable(@tbname, REPAIR_ALLOW_DATA_LOSS)
dbcc checktable(@tbname, REPAIR_REBUILD)
exec sp_dboption @dbname,'single user','false'

set @dbname = 'db3'
set @tbname='chr_data1'
use @dbname
exec sp_dboption @dbname, 'single user','true'
dbcc checktable(@tbname, REPAIR_ALLOW_DATA_LOSS)
dbcc checktable(@tbname, REPAIR_REBUILD)
exec sp_dboption @dbname,'single user','false'
set @tbname='chr_data2'
exec sp_dboption @dbname, 'single user','true'
dbcc checktable(@tbname, REPAIR_ALLOW_DATA_LOSS)
dbcc checktable(@tbname, REPAIR_REBUILD)
exec sp_dboption @dbname,'single user','false'
set @tbname='chr_data3'
exec sp_dboption @dbname, 'single user','true'
dbcc checktable(@tbname, REPAIR_ALLOW_DATA_LOSS)
dbcc checktable(@tbname, REPAIR_REBUILD)
exec sp_dboption @dbname,'single user','false'

 

--合区开始

--用以下SQL删除db1，db3的pcikuser有帐号无角色的数据
use db1
delete from pcikuser where userid not in (select id_user from chr)  --清理无角色ID
delete from chr where id_user not in (select userid from pcikuser)  --清理无ID角色
delete from [user] where account not in (select userid from pcikuser)  --清理无ID角色数据  
use db3
delete from pcikuser where userid not in (select id_user from chr)  --清理无角色ID
delete from chr where id_user not in (select userid from pcikuser)  --清理无ID角色
delete from [user] where account not in (select userid from pcikuser)  --清理无ID角色数据

--用以下SQL删除db1, db3的chr_data1-3表无用的用户数据
use db1
delete from chr_data1 where chr not in (select chr from chr_data1 where chr in (select chr1 from [user]) or chr in (select chr2 from [user]) or chr in (select chr3 from [user]))
delete from chr_data2 where chr not in (select chr from chr_data2 where chr in (select chr1 from [user]) or chr in (select chr2 from [user]) or chr in (select chr3 from [user]))
delete from chr_data3 where chr not in (select chr from chr_data3 where chr in (select chr1 from [user]) or chr in (select chr2 from [user]) or chr in (select chr3 from [user]))

use db3
delete from chr_data1 where chr not in (select chr from chr_data1 where chr in (select chr1 from [user]) or chr in (select chr2 from [user]) or chr in (select chr3 from [user]))
delete from chr_data2 where chr not in (select chr from chr_data2 where chr in (select chr1 from [user]) or chr in (select chr2 from [user]) or chr in (select chr3 from [user]))
delete from chr_data3 where chr not in (select chr from chr_data3 where chr in (select chr1 from [user]) or chr in (select chr2 from [user]) or chr in (select chr3 from [user]))

--更新db3中与db1相同的帐号，在后面加一个字母，比如'z'
update db3.dbo.pcikuser set userid = rtrim(userid) + 'z' where userid in (select userid from db1.dbo.pcikuser)

--将db3的pcikuser所有记录插入到db1中的pcikuser
insert into db1.dbo.pcikuser select * from db3.dbo.pcikuser

---pcikuser合并完成

 

--确定db1的user表chr3最大数并记录  6
select chr1,chr2,chr3 from db1.dbo.[user] order by chr1 desc

--确定db3的user表chr1最小数并记录  1
select chr1,chr2,chr3 from db3.dbo.[user] order by chr1

--将db3的[user]表的chr1,chr2,chr3值加上 94818+1-84859=9960
update db3.dbo.[user] set chr1=chr1+6, chr2=chr2+6, chr3=chr3+6

--更新db3的chr_data1,chr_data2,chr_data3的chr
update db3.dbo.chr_data1 set chr = chr + 6
update db3.dbo.chr_data2 set chr = chr + 6
update db3.dbo.chr_data3 set chr = chr + 6

--select chr from chr_data2 where chr in (select chr1 from [user]) or chr in (select chr2 from [user]) or chr in (select chr3 from [user])

--更新db3中[user]表与db1相同的帐号，在后面加一个字母，比如’z’
update db3.dbo.[user] set account = rtrim(account) + 'z' where account in (select account from db1.dbo.[user])

--将db3的[user]所有记录插入到db1中的[user]
insert into db1.dbo.[user] select * from db3.dbo.[user]

--确定db1的chr表cp_no最大数并记录   4
select cp_no from db1.dbo.chr order by cp_no desc

--确定db3的chr表cp_no最小数并记录   1
select cp_no from db3.dbo.chr order by cp_no

--更新db3的chr表cp_no 加4357(db1的chr表cp_no最大数并记录   4487 + 1   -    db3的chr表cp_no最小数并记录   131)
update db3.dbo.chr set cp_no = cp_no + 4

--将db3中chr表的cp_name同名角色重命名
update db3.dbo.chr set cp_name = rtrim(cp_name) + 'z' where cp_name in (select cp_name from db1.dbo.chr)

--将db3中chr表的id_user同名帐号重命名
update db3.dbo.chr set id_user = rtrim(id_user) + 'z' where id_user in (select userid from db1.dbo.pcikuser)

--合并chr表
insert into db1.dbo.chr select * from db3.dbo.chr
--合并chr_data1表
insert into db1.dbo.chr_data1 select * from db3.dbo.chr_data1
--合并chr_data2表
insert into db1.dbo.chr_data2 select * from db3.dbo.chr_data2
--合并chr_data3表
insert into db1.dbo.chr_data3 select * from db3.dbo.chr_data3

-----------恢复准备工作中的标识及触发器

--合区完毕