批量添加、批量修改

 

--查询满足条件数据
DECLARE @Table NVARCHAR(10)
SELECT @Table=ItemValue FROM dbo.Sys_SysParameterItems WHERE SysParameterKeyId='6F74A9E3-8346-4BFE-81A3-C30547542FE2' AND itemno='70'
IF @Table='A'
BEGIN
--查询当日满足条件的房源信息
SELECT DISTINCT NEWID()Id ,P.KeyId AS PropertyKeyId,p.PropertyStatusCategory,p.TrustType,P.PropertyNo AS bianhao,er.ChannelValue,
estate.EstateName+' '+CONVERT(VARCHAR, p.CountF)+'室'+CONVERT(VARCHAR,p.CountT)+'厅'+CONVERT(VARCHAR,p.CountW) +'卫 '+ISNULL(att.AttName,'') AS title,
p.CountF,p.CountT,p.CountW,bul.FloorAll,p.Floor,p.Square,ISNULL(chaox.ItemName,'其他') AS ItemName,(CASE WHEN year(convert(datetime,bul.CompleteYear))='1900' THEN '1901' ELSE year(convert(datetime,bul.CompleteYear))END ) as CompleteYear,ISNULL(zhuangxiu.ItemName,'毛坯') AS zhuangxiuqingkuang,
ISNULL( yongtu.ItemName,'其他') AS fangwuleixing,bul.BuildingName AS loudong,bul.BuildingName as danyuan,h.HouseNo AS menpaihao,
bul.BuildingName AS loudongdanwei,bul.BuildingName AS danyuandanwei,
(CASE WHEN p.TrustType=1 THEN sale.SalePrice*10000 WHEN p.TrustType=3 THEN sale.SalePrice*10000 ELSE NULL END) jiage,
(CASE WHEN p.TrustType=2 THEN rent.RentPrice WHEN p.TrustType=3 THEN rent.RentPrice ELSE NULL END) rentjiage,
(case when p.TrustType=1 THEN (SELECT TOP 1 LEFT(pe.PropertySaleAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1)
when p.TrustType=3 THEN (SELECT TOP 1 LEFT(pe.PropertySaleAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1) ELSE NULL END) fangyuanxiangqing,
(case when p.TrustType=2 THEN (SELECT TOP 1 LEFT(pe.PropertyAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1)
when p.TrustType=3 THEN (SELECT TOP 1 LEFT(pe.PropertyAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1) ELSE NULL END) rentfangyuanxiangqing,
(CASE WHEN p.TrustType=2 THEN 1 WHEN p.TrustType=3 THEN 1 ELSE NULL END) rentType,
(CASE WHEN p.TrustType=2 THEN fukuan.ItemName WHEN p.TrustType=3 THEN fukuan.ItemName ELSE NULL END) paymentTerms,
GETDATE() AS CreateTime,GETDATE() AS UpdateTime
INTO #temp
FROM [dbo].[VW_Adm_A_PushPropertys] p
LEFT JOIN dbo.Est_Estates estate ON estate.KeyId=p.EstateKeyId
LEFT JOIN dbo.Est_Buildings bul ON bul.KeyId=p.BuildingKeyId
LEFT JOIN dbo.Est_Houses h ON h.KeyId=p.HouseKeyId
LEFT JOIN [dbo].[Est_EstateParameterRelations] er ON p.EstateKeyId=er.EstateKeyId AND er.SysParameterItemKeyId='B5A6301D-EA27-4B41-B24E-857DB33C4CC6'
LEFT JOIN dbo.Sys_SysParameterItems chaox ON p.HouseDirectionKeyId=chaox.KeyId
LEFT JOIN dbo.Sys_SysParameterItems zhuangxiu ON zhuangxiu.KeyId=p.DecorationSituationKeyId
LEFT JOIN dbo.Sys_SysParameterItems yongtu ON yongtu.KeyId=p.PropertyUsageKeyId
LEFT JOIN VW_Adm_A_property_Attribute att ON att.keyid=p.KeyId
LEFT JOIN dbo.Pro_RentTrusts rent ON rent.PropertyKeyId=p.KeyId
LEFT JOIN dbo.Pro_SaleTrusts sale ON sale.PropertyKeyId=p.KeyId
LEFT JOIN dbo.Sys_SysParameterItems fukuan ON fukuan.KeyId=rent.PropertyRentPaymentTypeKeyId
LEFT JOIN dbo.Adm_PushProperty push ON push.PropertyKeyId=p.KeyId
WHERE push.KeyId IS NULL AND p.IsDelete=0 AND p.PropertyStatusCategory = 1
AND yongtu.KeyId IN('690b45f8-5450-4c26-b3cc-2ffa24d5e2ef','55c74e49-07bb-c488-faeb-08d2f60a03e8','3854d76d-1a9c-41ad-8443-381e4df1d60f','24990581-27dd-4690-9359-73fe6911b033','19961612-b328-44c3-bf36-9e5bf8ebc694','d21842b6-de49-4d2f-8838-a252b4e41fab','942069b8-87ea-4e9e-bfb6-b9ba31b3a3a4','6ea6f89e-3e4d-4f64-a14e-c8fea294caab','e5a98685-f85f-cff9-faeb-08d2f60a03e8')
AND p.EstateKeyId NOT IN('a9f870b4-e5cb-4b26-aca4-8329cb406ee8')
--插入到推送表
SELECT COUNT(1) FROM #temp
if @@rowcount>0
BEGIN
INSERT INTO dbo.Adm_PushProperty
( KeyId ,PropertyKeyId ,PropertyStatusCategory , TrustType ,
bianhao ,xiaoquId ,title , shi , ting ,
wei ,zonglouceng ,suozailouceng ,mianji ,chaoxiang ,
jianzhuniandai ,zhuangxiuqingkuang , fangwuleixing ,loudong ,
danyuan ,menpaihao , loudongdanwei , danyuandanwei , jiage ,
rentjiage , fangyuanxiangqing , rentfangyuanxiangqing , rentType ,
paymentTerms ,
CreateTime ,
UpdateTime
)
SELECT * FROM #temp
END
--查询满足条件房源修改到推送表中
SELECT DISTINCT NEWID()Id ,P.KeyId AS PropertyKeyId,p.PropertyStatusCategory,p.TrustType,P.PropertyNo AS bianhao,er.ChannelValue,
(estate.EstateName+' '+CONVERT(VARCHAR, p.CountF)+'室'+CONVERT(VARCHAR,p.CountT)+'厅'+CONVERT(VARCHAR,p.CountW) +'卫 ')+ISNULL(att.AttName,'') AS title,
p.CountF,p.CountT,p.CountW,bul.FloorAll,p.Floor,p.Square,ISNULL(chaox.ItemName,'其他') AS chaox,(CASE WHEN year(convert(datetime,bul.CompleteYear))='1900' THEN '1901' ELSE year(convert(datetime,bul.CompleteYear))END ) as CompleteYear,ISNULL(zhuangxiu.ItemName,'毛坯') AS zhuangxiuqingkuang,
ISNULL( yongtu.ItemName,'其他') AS fangwuleixing,bul.BuildingName AS loudong,bul.BuildingName as danyuan,h.HouseNo AS menpaihao,
bul.BuildingName AS loudongdanwei,bul.BuildingName AS danyuandanwei,
(CASE WHEN p.TrustType=1 THEN sale.SalePrice*10000 WHEN p.TrustType=3 THEN sale.SalePrice*10000 ELSE NULL END) jiage,
(CASE WHEN p.TrustType=2 THEN rent.RentPrice WHEN p.TrustType=3 THEN rent.RentPrice ELSE NULL END) rentjiage,
(case when p.TrustType=1 THEN (SELECT TOP 1 LEFT(pe.PropertySaleAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1)
when p.TrustType=3 THEN (SELECT TOP 1 LEFT(pe.PropertySaleAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1) ELSE NULL END) fangyuanxiangqing,
(case when p.TrustType=2 THEN (SELECT TOP 1 LEFT(pe.PropertyAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1)
when p.TrustType=3 THEN (SELECT TOP 1 LEFT(pe.PropertyAssess,300) FROM Pro_PropertyExpands pe WHERE pe.PropertyKeyId=p.KeyId AND pe.Type=1) ELSE NULL END) rentfangyuanxiangqing,
(CASE WHEN p.TrustType=2 THEN 1 WHEN p.TrustType=3 THEN 1 ELSE NULL END) rentType,
(CASE WHEN p.TrustType=2 THEN fukuan.ItemName WHEN p.TrustType=3 THEN fukuan.ItemName ELSE NULL END) paymentTerms,
GETDATE() AS CreateTime,GETDATE() AS UpdateTime
INTO #updateTemp
FROM [dbo].[VW_Adm_A_PushPropertys] p
LEFT JOIN VW_Adm_PushProperty_ActionHistoryLogs viewPush ON viewPush.SourceObjectKeyId=p.KeyId
LEFT JOIN dbo.Est_Estates estate ON estate.KeyId=p.EstateKeyId
LEFT JOIN dbo.Est_Buildings bul ON bul.KeyId=p.BuildingKeyId
LEFT JOIN dbo.Est_Houses h ON h.KeyId=p.HouseKeyId
LEFT JOIN [dbo].[Est_EstateParameterRelations] er ON p.EstateKeyId=er.EstateKeyId AND er.SysParameterItemKeyId='B5A6301D-EA27-4B41-B24E-857DB33C4CC6'
LEFT JOIN dbo.Sys_SysParameterItems chaox ON p.HouseDirectionKeyId=chaox.KeyId
LEFT JOIN dbo.Sys_SysParameterItems zhuangxiu ON zhuangxiu.KeyId=p.DecorationSituationKeyId
LEFT JOIN dbo.Sys_SysParameterItems yongtu ON yongtu.KeyId=p.PropertyUsageKeyId
LEFT JOIN VW_Adm_A_property_Attribute att ON att.keyid=p.KeyId
LEFT JOIN dbo.Pro_RentTrusts rent ON rent.PropertyKeyId=p.KeyId
LEFT JOIN dbo.Pro_SaleTrusts sale ON sale.PropertyKeyId=p.KeyId
LEFT JOIN dbo.Sys_SysParameterItems fukuan ON fukuan.KeyId=rent.PropertyRentPaymentTypeKeyId
RIGHT JOIN dbo.Adm_PushProperty push ON push.PropertyKeyId=p.KeyId
LEFT JOIN #temp temp ON temp.PropertyKeyId=push.PropertyKeyId
WHERE p.KeyId IS NOT NULL AND temp.PropertyKeyId IS NULL
AND (DATEDIFF(mi, push.UpdateTime,viewPush.CreateTime ) > 1 or push.xiaoquId!=er.ChannelValue)
AND yongtu.KeyId IN('690b45f8-5450-4c26-b3cc-2ffa24d5e2ef','55c74e49-07bb-c488-faeb-08d2f60a03e8','3854d76d-1a9c-41ad-8443-381e4df1d60f','24990581-27dd-4690-9359-73fe6911b033','19961612-b328-44c3-bf36-9e5bf8ebc694','d21842b6-de49-4d2f-8838-a252b4e41fab','942069b8-87ea-4e9e-bfb6-b9ba31b3a3a4','6ea6f89e-3e4d-4f64-a14e-c8fea294caab','e5a98685-f85f-cff9-faeb-08d2f60a03e8')--AND ISNULL(att.AttName,'') IS NOT NULL
AND p.EstateKeyId NOT IN('a9f870b4-e5cb-4b26-aca4-8329cb406ee8')
--修改推送表数据
SELECT COUNT(1) FROM #temp
if @@rowcount>0
BEGIN
UPDATE push
SET push.PropertyStatusCategory=t.PropertyStatusCategory,
push.TrustType=t.TrustType,
push.bianhao=t.bianhao,
push.xiaoquId=t.ChannelValue,
push.title=t.title,
push.shi=t.CountF,
push.ting=t.CountT,
push.wei=t.CountW,
push.zonglouceng=t.FloorAll,
push.suozailouceng=t.Floor,
push.mianji=t.Square,
push.chaoxiang=t.chaox,
push.jianzhuniandai=t.CompleteYear,
push.zhuangxiuqingkuang=t.zhuangxiuqingkuang,
push.fangwuleixing=t.fangwuleixing,
push.loudong=t.loudong,
push.danyuan=t.danyuan,
push.menpaihao=t.menpaihao,
push.loudongdanwei=t.loudongdanwei,
push.danyuandanwei=t.danyuandanwei,
push.jiage=t.jiage,
push.rentjiage=t.rentjiage,
push.fangyuanxiangqing=t.fangyuanxiangqing,
push.rentfangyuanxiangqing=t.rentfangyuanxiangqing,
push.rentType=t.rentType,
push.paymentTerms=t.paymentTerms,
push.UpdateTime=GETDATE()
FROM dbo.Adm_PushProperty push, #updateTemp T
WHERE t.PropertyKeyId=push.PropertyKeyId
END
DROP TABLE #temp
DROP TABLE #updateTemp
END