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

USE [db]
-- 定义变量;
DECLARE @COUNT INT				-- 数量;
DECLARE @SUM 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, phone1, phone2, name1, name2 INTO #temp_dindan FROM dindan
-- 获取临时表最大ID;
SELECT @COUNT = MAX(AutoId) FROM #temp_dindan

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

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