SQL常用备份更新操作
2022/7/21 2:25:02
本文主要是介绍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];
这篇关于SQL常用备份更新操作的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-13TiDB + ES:转转业财系统亿级数据存储优化实践
- 2024-05-09“2024鸿蒙零基础快速实战-仿抖音App开发(ArkTS版)”实战课程已上线
- 2024-05-09聊聊如何通过arthas-tunnel-server来远程管理所有需要arthas监控的应用
- 2024-05-09log4j2这么配就对了
- 2024-05-09nginx修改Content-Type
- 2024-05-09Redis多数据源,看这篇就够了
- 2024-05-09Google Chrome驱动程序 124.0.6367.62(正式版本)去哪下载?
- 2024-05-09有没有大佬知道这种数据应该怎么抓取呀?
- 2024-05-09这种运行结果里的10.100000001,怎么能最快改成10.1?
- 2024-05-09企业src漏洞挖掘-有意思的命令执行