lyfz_repos/经典版源码/document/数据库维护脚本/将dindan表客户资料更新到client表.sql

USE [db]
-- 定义变量;
DECLARE @COUNT INT				-- 数量;
DECLARE @SIZE INT				-- 数量;
DECLARE @OrderId NVARCHAR(80)	-- 订单号;
DECLARE @Phone1 NVARCHAR(80)	-- 电话1;
DECLARE @Phone2 NVARCHAR(80)	-- 电话2;
DECLARE @Name1 NVARCHAR(80)		-- 姓名1;
DECLARE @Name2 NVARCHAR(80)		-- 姓名2;

-- 查询出所有订单的电话,并插入到临时表中;
SELECT IDENTITY(INT,1,1) AS AutoId, id, name1, name2, phone1, phone2 INTO #temp_dindan FROM dindan
-- 获取临时表最大ID;
SELECT @COUNT = MAX(AutoId) FROM #temp_dindan

-- 循环将dindan表里的订单电话更新到client对应的订单记录中;
WHILE @COUNT > 0
BEGIN
	SELECT @OrderId = id , @Name1 = name1, @Name2 =  name2, @Phone1 = phone1, @Phone2 = phone2 FROM #temp_dindan WHERE AutoId = @COUNT
	SELECT @SIZE = COUNT(*) FROM client WHERE id = @OrderId
	if @SIZE > 0
		UPDATE client SET name1 = @Name1, name2 = @Name2, phone1 = @Phone1, phone2 = @Phone2 WHERE id = @OrderId
	ELSE
		INSERT INTO client([id],[name1],[name2],[phone1],[phone2])VALUES(@OrderId, @Name1, @Name2, @Phone1, @Phone2)
	SET @COUNT = @COUNT -1
	SET @SIZE = 0
END

-- 最后,清除临时表;
DROP TABLE #temp_dindan