34,588
社区成员
发帖
与我相关
我的任务
分享
/*
环境:SQLSERVER 2012
描述:data_residentProject 有124个字段,为了看着方便
--省略了其他字段(目前数据量在千万级别,以后会拆)
ProvinceCode_RegionCode_CountyCode 组合索引
--data_residentProjectTemp 作为临时表,合并成功的数据将被删除(数据量应该不会很大)
*/
DECLARE @ProvinceCode NVARCHAR(10)='28';
DECLARE @RegionCode NVARCHAR(10)='76';
DECLARE @CountyCode NVARCHAR(10)='86';
DECLARE @CreatePerson NVARCHAR(10)='admin';
BEGIN TRAN
DECLARE @tran_error INT;
SET @tran_error = 0;
BEGIN TRY
-- merge begin
MERGE INTO data_residentProject AS T1
USING (
SELECT *
FROM data_residentProjectTemp AS T
WHERE T.[TempStatus] = 0
AND T.Submit = 0
AND T.ProvinceCode = @ProvinceCode
AND T.RegionCode = @RegionCode
AND T.CountyCode = @CountyCode
AND T.CreatePerson = @CreatePerson
AND NOT EXISTS(
SELECT 1 FROM data_residentProjectTemp T2
WHERE T2.ID > T.ID
AND T2.[TempStatus] = 0
AND T2.Submit = 0
AND T2.ProvinceCode = @ProvinceCode
AND T2.RegionCode = @RegionCode
AND T2.CountyCode = @CountyCode
AND T.CommunityName = T2.CommunityName
AND T2.CreatePerson = @CreatePerson
)
) AS T2
ON T1.ProvinceCode=T2.ProvinceCode AND T1.RegionCode=T2.RegionCode AND T1.CountyCode=T2.CountyCode AND T1.CommunityName=T2.CommunityName
WHEN MATCHED
THEN UPDATE SET T1.CommunityID=T2.CommunityID,T1.CommunityName=T2.CommunityName
WHEN NOT MATCHED
THEN INSERT([CommunityID],[CommunityName]) VALUES(T2.[CommunityID],T2.[CommunityName]);
SET @tran_error = @tran_error + @@ERROR;
--merge end
--update submit status
DELETE data_residentProjectTemp
WHERE [CountyCode] = @CountyCode
AND [TempStatus] = 0
AND [Submit] = 0
AND [ProvinceCode] = @ProvinceCode
AND [RegionCode] = @RegionCode
AND [CreatePerson] = @CreatePerson;
SET @tran_error = @tran_error + @@ERROR;
END TRY
BEGIN CATCH
SET @tran_error = @tran_error + 1
END CATCH
IF(@tran_error > 0)
BEGIN
ROLLBACK TRAN;
END
ELSE
BEGIN
COMMIT TRAN;
END