--声明数据库
use AionWorld_110
--声明变量
DECLARE @user_item_table_name varchar(90)
DECLARE @user_item_insert varchar(90)
DECLARE @user_item_select varchar(90)
DECLARE @user_item_update varchar(90)
DECLARE @user_item_drop varchar(90)
--
DECLARE @user_item_option_table_name varchar(90)
DECLARE @user_item_option_insert varchar(90)
DECLARE @user_item_option_select varchar(90)
DECLARE @user_item_option_update varchar(90)
DECLARE @user_item_option_drop varchar(90)
--
DECLARE @user_item_polish_table_name varchar(90)
DECLARE @user_item_polish_insert varchar(90)
DECLARE @user_item_polish_select varchar(90)
DECLARE @user_item_polish_update varchar(90)
DECLARE @user_item_polish_delete varchar(90)
DECLARE @user_item_polish_drop varchar(90)
DECLARE @old_char_id int
DECLARE @new_char_id int
--赋值变量
set @old_char_id = 1000
set @new_char_id = 1000
--临时表名
set @user_item_table_name = 'user_item_' + @old_char_id + '_to_' + @new_char_id
set @user_item_option_table_name = 'user_item_option_' + @old_char_id + '_to_' + @new_char_id
set @user_item_polish_table_name = 'user_item_polish_' + @old_char_id + '_to_' + @new_char_id
--导出指定玩家数据
set @user_item_select = '
SELECT newid() as guid
,[id]
,[char_id]
,[name_id]
,[slot_id]
,[amount]
,[slot]
,[warehouse]
,[create_date]
,[update_date]
,[producer]
,[tid]
,[expired_time]
,[buy_amount]
,[buy_duration]
,[main_item_dbid]
,[dynamic_property]
,[import_id]
,[export_id]
,[server_of_origin]
INTO ' + @user_item_table_name + '
FROM user_item
WHERE char_id = ' + @old_char_id
exec(@user_item_select)
set @user_item_option_select = '
SELECT ui.guid
,uio.[id]
,uio.[char_id]
,uio.[soul_bound]
,uio.[enchant_count]
,uio.[skin_name_id]
,uio.[stat_enchant_name0]
,uio.[stat_enchant_0]
,uio.[stat_enchant_val0]
,uio.[stat_enchant_name1]
,uio.[stat_enchant_1]
,uio.[stat_enchant_val1]
,uio.[stat_enchant_name2]
,uio.[stat_enchant_2]
,uio.[stat_enchant_val2]
,uio.[stat_enchant_name3]
,uio.[stat_enchant_3]
,uio.[stat_enchant_val3]
,uio.[stat_enchant_name4]
,uio.[stat_enchant_4]
,uio.[stat_enchant_val4]
,uio.[stat_enchant_name5]
,uio.[stat_enchant_5]
,uio.[stat_enchant_val5]
,uio.[option_count]
,uio.[dye_info]
,uio.[proc_tool_nameid]
,uio.[obtain_skin_type]
,uio.[expire_skin_time]
,uio.[expire_dye_time]
,uio.[random_option]
,uio.[limit_enchant_count]
,uio.[reidentify_count]
,uio.[authorize_count]
,uio.[vanish_point]
,uio.[parts_enchant_name0]
,uio.[parts_enchant_name1]
,uio.[parts_enchant_name2]
,uio.[parts_enchant_name3]
,uio.[parts_enchant_name4]
,uio.[parts_enchant_name5]
,uio.[parts_option_count]
INTO ' + @user_item_option_table_name + '
FROM [dbo].[user_item_option] AS uio
INNER JOIN ' + @user_item_table_name + ' ui ON ui.id=uio.id
WHERE char_id = ' + @old_char_id
exec(@user_item_option_select)
set @user_item_polish_select = '
SELECT ui.guid
,uip.[id]
,uip.[name_id]
,uip.[random_id]
,uip.[polish_point]
FROM [dbo].[user_item_polish] AS uip
INNER JOIN ' + @user_item_table_name + ' ui ON ui.id=uip.id
WHERE uip.id in (SELECT id FROM ' + @user_item_table_name + ')'
exec(@user_item_polish_select)
--删除原有物品数据
DELETE FROM user_item WHERE char_id = @old_char_id
DELETE FROM user_item_option WHERE char_id = @old_char_id
set @user_item_polish_delete = 'DELETE FROM user_item_polish WHERE id in (SELECT id FROM ' + @user_item_table_name + ')'
exec(@user_item_polish_delete)
--修改临时表玩家编号
set @user_item_update = '
UPDATE ' + @user_item_table_name + '
SET char_id = ' + @new_char_id
exec(@user_item_update)
set @user_item_option_update = '
UPDATE ' + @user_item_option_table_name + '
SET char_id = ' + @new_char_id
exec(@user_item_option_update)
--导入数据到物品表
alter table user_item add guid uniqueidentifier not null default newid()
alter table user_item_option add guid uniqueidentifier not null default newid()
alter table user_item_polish add guid uniqueidentifier not null default newid()
set @user_item_insert = '
INSERT INTO user_item
SELECT [id]
,[char_id]
,[name_id]
,[slot_id]
,[amount]
,[slot]
,[warehouse]
,[create_date]
,[update_date]
,[producer]
,[tid]
,[expired_time]
,[buy_amount]
,[buy_duration]
,[main_item_dbid]
,[dynamic_property]
,[import_id]
,[export_id]
,[server_of_origin]
,[guid]
FROM ' + @user_item_table_name
exec(@user_item_insert)
set @user_item_option_update ='
UPDATE ' + @user_item_option_table_name + ' AS uip
SET uip.id = ui.id
FROM [dbo].[user_item] AS ui
INNER JOIN ' + @user_item_option_table_name + ' ui ON ui.guid=uip.guid'
exec(@user_item_option_update)
set @user_item_option_insert = '
INSERT INTO user_item_option
SELECT * FROM ' + @user_item_option_table_name
exec(@user_item_option_insert)
set @user_item_polish_update ='
UPDATE ' + @user_item_polish_table_name + ' AS uip
SET uip.id = ui.id
FROM [dbo].[user_item] AS ui
INNER JOIN ' + @user_item_polish_table_name + ' ui ON ui.guid=uip.guid'
exec(@user_item_polish_update)
set @user_item_polish_insert = '
INSERT INTO user_item_polish
SELECT * FROM ' + @user_item_polish_table_name
exec(@user_item_polish_insert)
--删除临时表
alter table user_item drop column guid;
alter table user_item_option drop column guid;
alter table user_item_polish drop column guid;
set @user_item_drop = 'DROP table ' + @user_item_table_name
exec(@user_item_drop)
set @user_item_option_drop = 'DROP table ' + @user_item_option_table_name
exec(@user_item_option_drop)
set @user_item_polish_drop = 'DROP table ' + @user_item_polish_table_name
exec(@user_item_polish_drop)