lyfz_repos/经典版源码/document/数据库维护脚本/从dindan表里更新client表.sql

-- ============================================= 
-- 程序编写： Jeff
-- 版    本:  V 1.0
-- 建立日期： 2015-05-19
-- 功能说明： 从dindan表里更新client表;
-- 备    注： 

-- 修改日期：
-- 修改说明：
-- ============================================= 
use db

declare @order nvarchar(36)				-- 订单号;
declare @name1 nvarchar(24)				-- 姓名1;
declare @name2 nvarchar(24)				-- 姓名2;
declare @phone1 nvarchar(12)			-- 手机1;
declare @phone2 nvarchar(12)			-- 手机2;
declare @time3 nvarchar(16)				-- 婚庆日期;
declare @curIndex int					-- 当前索引;
declare @count	int						-- 查找数量;

-- 将dindan表重新生成到新表里;
select identity(int,1,1) as autoid,* into dindan_temp from dindan

-- 设置curIndex索引的最大值;
select @curIndex = max(autoid) from dindan_temp

-- 根据新表的autoid来逐步更新client数据;
while(@curIndex > 0)
begin
	select  @order = id, @name1 = name1, @name2 = name2, @phone1 = phone1, @phone2 = phone2, @time3 = time3
	from dindan_temp where autoid = @curIndex

	print @order
	print @name1
	print @name2
	print @phone1
	print @phone2
	print @time3

	select @count = count(*) from client where id = @order
	
	if( @count > 0 )
		update client set name1 = @name1, name2 = @name2, phone1 = @phone1, phone2 = @phone2,time3 = @time3 where id = @order
	else
		insert client(id,name1,name2,phone1,phone2,time3) values (@order,@name1,@name2,@phone1,@phone2,@time3) 
	
	set @curIndex = @curIndex - 1
end

-- 最后,删除临时表;
drop table dindan_temp

/*

declare @order nvarchar(36)				-- 订单号;
declare @name1 nvarchar(24)				-- 姓名1;
declare @name2 nvarchar(24)				-- 姓名2;
declare @phone1 nvarchar(12)			-- 手机1;
declare @phone2 nvarchar(12)			-- 手机2;
--declare @birthday1 nvarchar(16)			-- 生日1;
--declare @birthday2 nvarchar(16)			-- 生日2;
declare @time3 nvarchar(16)				-- 婚庆日期;
--declare @check1 nvarchar(4)				-- 是否农历1;
--declare @check2 nvarchar(4)				-- 是否农历2;
--declare @check3 nvarchar(4)				-- 是否农历3;
--declare @qq1	nvarchar(16)			-- QQ1;
--declare @qq2	nvarchar(16)			-- QQ2;
--declare @adr1	nvarchar(64)			-- 地址1;
--declare @adr2	nvarchar(64)			-- 地址2;
--declare @sex	nvarchar(4)				-- 性别;
--declare @zodiac nvarchar(4)			-- 生肖;

declare @curIndex int					-- 当前索引;
declare @count	int						-- 查找数量;

-- 将dindan表重新生成到新表里;
select identity(int,1,1) as autoid,* into dindan_temp from dindan

-- 设置curIndex索引的最大值;
select @curIndex = max(autoid) from dindan_temp

-- 根据新表的autoid来逐步更新client数据;
while(@curIndex > 0)
begin
	select  @order = id, @name1 = name1, @name2 = name2, @phone1 = phone1, @phone2 = phone2, @time3 = time3
	from dindan_temp where autoid = @curIndex
	
	select @count = count(*) from client where id = @order
	
	if( @count > 0 )
		update client set name1 = @name1, name2 = @name2, @phone1 = phone1, @phone2 = phone2,@time3 = time3 where id = @order
	else
		insert client(id,name1,name2,phone1,phone2,time3) values (@order,@name1,@name2,@phone1,@phone2,@time3) 
	
	set @curIndex = @curIndex - 1
end

-- 最后,删除临时表;
drop table dindan_temp

*/