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];