我需要创建一个存储的过程,用于将生产数据库中某个表中的数据移动到2010年之前到存档数据库(创建的结构与生产相同)。我使用的方法是将数据从生产复制到存档,并删除生产中存在存档的数据。这是代码:
--## INSERT data from Production DB to Archive DB
--tableMaster
INSERT INTO [DB_Archive].[dbo].[tableMaster]
select * from [DB_Production].[dbo].[tableMaster]
where QuoDate < '2010-01-01 00:00:00.000'
--tableDetail
INSERT INTO [DB_Archive].[dbo].[tableDetail]
select * from [DB_Production].[dbo].[tableDetail] tblDet
where exists (select tblMaster.QuoNo,tblMaster.AgentCode from [DB_Archive].[dbo].[tableMaster] tblMaster
where tblDet.QuoNo = tblMaster.QuoNo)
--tableSubDetail1
INSERT INTO [DB_Archive].[dbo].[tableSubDetail1]
select * from [DB_Production].[dbo].[tableSubDetail1] tblsub1
where exists (select tblMaster.QuoNo,tblMaster.AgentCode from [DB_Archive].[dbo].[tableMaster] tblMaster
where tblsub1.QuoNo = tblMaster.QuoNo)
--tableSubDetail2
INSERT INTO [DB_Archive].[dbo].[tableSubDetail2]
select * from [DB_Production].[dbo].[tableSubDetail2] tblsub2
where exists (select tblMaster.QuoNo,tblMaster.AgentCode from [DB_Archive].[dbo].[tableMaster] tblMaster
where tblsub2.QuoNo = tblMaster.QuoNo)
--## DELETE data from Production DB
--tableMaster
DELETE tblM FROM [DB_Archive].[dbo].[tableMaster] tblM
Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableMaster] bmas
where tblM.QuoNo = bmas.QuoNo)
--tableDetail
DELETE tblD FROM [DB_Archive].[dbo].[tableDetail] tblD
Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableDetail] bmas
where tblM.QuoNo = bmas.QuoNo)
--tableSubDetail1
DELETE tblS1 FROM [DB_Archive].[dbo].[tableSubDetail1] tblS1
Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableSubDetail1] bmas
where tblM.QuoNo = bmas.QuoNo)
--tableSubDetail2
DELETE tblS2 FROM [DB_Archive].[dbo].[tableSubDetail2] tblS2
Where exists (select bmas.QuoNo from [DB_Archive].[dbo].[tableSubDetail2] bmas
where tblM.QuoNo = bmas.QuoNo)
上面仅适用于 4 个表,我有 30++ 表使用相同的脚本。你能告诉我如何使这段代码简单而简短吗?谢谢
您可以在单个语句中组合 INSERT 和 DELETE:
DELETE [DB_Production].[dbo].[tableDetail]
output deleted.col1, deleted.col2, etc into [DB_Archive].[dbo].[tableDetail]
where exists (select * from [DB_Archive].[dbo].[tableMaster] tblMaster where [DB_Production].[dbo].[tableDetail].QuoNo = tblMaster.QuoNo)