SQL常用备份更新操作
--备份报销推送记录表
DECLARE @ctime NVARCHAR(36) = '2022-06-13 00:42:26'
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[p_Provider2Bank_20220624]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DECLARE @sql NVARCHAR(MAX)
SET @sql=N'SELECT * INTO [dbo].[p_Provider2Bank_20220624] FROM dbo.p_Provider2Bank a WHERE a.x_BankNum IS NULL AND a.CreatedTime>'''+@ctime+''''
EXEC (@sql)
--获取异常数据,插入到临时表，101条异常数据
SELECT
*
INTO [#myp_Provider2BankAdjust]
FROM p_Provider2BankAdjust b WHERE b.Provider2BankGUID IN
(
SELECT a.Provider2BankGUID FROM p_Provider2Bank a WHERE a.x_BankNum IS NULL AND a.CreatedTime>'2022-06-13 00:42:26'
) AND b.x_BankNum IS NOT NULL ORDER BY b.AdjustGUID,b.Provider2BankAdjustGUID
SELECT * FROM [#myp_Provider2BankAdjust]
--获取调整版本最后一次保留的银行账户信息
SELECT
A.Provider2BankAdjustGUID,
B.Provider2BankGUID,
B.BankName AS newBankName,
B.BankAccount AS newBankAccount,
B.BankAddress AS newBankAddress,
B.x_BankNum AS newBankNum,
B.x_Bz AS newBz,
B.x_BankArchivesGUID AS newBankArchivesGUID,
B.x_IsEnable AS newIsEnable,
B.x_FwUnitGuidList AS newFwUnitGuidList,
B.x_FwUnitNameList AS newFwUnitNameList,
B.x_Khzm AS newKhzm
INTO [#myp_Provider2BankForLastVersion]
FROM
(
SELECT
count(Provider2BankAdjustGUID) ss ,
max(CreatedTime) maxdate,
Provider2BankAdjustGUID
from [#myp_Provider2BankAdjust]
group by Provider2BankAdjustGUID
) A
LEFT JOIN [#myp_Provider2BankAdjust] B on A.Provider2BankAdjustGUID=B.Provider2BankAdjustGUID and A.maxdate=B.CreatedTime
--开始执行更新(批量，共101条数据)
UPDATE
B
SET
B.x_BankNum = A.newBankNum,
B.x_BankArchivesGUID = A.newBankArchivesGUID,
B.x_Bz = A.newBz,
B.x_IsEnable = A.newIsEnable,
B.x_FwUnitGuidList = A.newFwUnitGuidList,
B.x_FwUnitNameList = A.newFwUnitNameList,
B.x_Khzm = A.newKhzm
FROM
p_Provider2Bank B JOIN [#myp_Provider2BankForLastVersion] A ON ( B.Provider2BankGUID = A.Provider2BankGUID )
WHERE B.x_BankNum IS NULL AND B.CreatedTime>'2022-06-13 00:42:26'
--删除临时表
DROP TABLE [#myp_Provider2BankAdjust];
DROP TABLE [#myp_Provider2BankForLastVersion];
