两张表数据同步

用EXcel导入数据时候，有需求是：

1. Excel中有的数据，系统表中没有的就插入；

1. Excel中有的数据，系统表中也有的就更新；

1. Excel中没有的数据，系统表中有的就删除。

解决方案：

1. 创建物理表Temp，将数据先导入至Temp表中。

分为2种情况：

a:有导入失败记录的数据时，就将Temp表清空。

b:全部数据导入成功时，就执行下面数据同步的存储过程，将Temp表数据和业务数据表进行关联，做逻辑处理，数据同步完后，再执行代码将Temp表数据清空。

USE [Epms3]

GO

--FourGProjectInWeekReportTemp数据同步到FourGProjectInWeekReport

ALTER PROC [dbo].[TempToPPFourGProjectInWeekReport]

AS

BEGIN

DELETE FROM A

FROM EPMSReport.dbo.FourGProjectInWeekReport AS A

LEFT OUTER JOIN EPMSReport.dbo.FourGProjectInWeekReportTemp AS LSA

ON A.SiteName = LSA.SiteName --要求是站点名称唯一

AND LSA.[year] = A.[year]

AND LSA.WeekNum = A.WeekNum

AND LSA.CompanyName = A.CompanyName

WHERE LSA.SiteName IS NULL

and A.Year=(Select top 1 [YEAR] from EPMSReport.dbo.FourGProjectInWeekReportTemp)

and A.WeekNum=(Select top 1 WeekNum from EPMSReport.dbo.FourGProjectInWeekReportTemp)

and A.CompanyName in (Select distinct CompanyName from EPMSReport.dbo.FourGProjectInWeekReportTemp)

INSERT INTO EPMSReport.dbo.FourGProjectInWeekReport

(

A.Year,

A.WeekNum,

A.CompanyID,

A.CompanyName,

A.SiteName,

A.Longitude,

A.Latitude,

A.PropertyType,

A.SiteType,

A.SFType,

A.SiteIsChange,

A.EManufacturers,

A.ZConfiguration,

A.BBUNum,

A.RRUNum,

A.CSiteDate,

A.CCrenovationDate,

A.CTransmissionAccessDate,

A.CEquipmentInstallationDate,

A.CEngineeringOpeningDate,

A.CFullyOpenedDate,

A.CSsVerificationDate,

A.SortOrder,

A.LastModityID,

A.LastModityName,

A.LastModfiyTime,

A.MatingDesignDate,

A.MasterDeviceDesignDate,

A.RemarkOne,

A.RemarkTwo,

A.SubProjectName,

A.NewStationActualSiteName,

A.NewStationCompletedCablePipe,

A.NewTechnologyTypes,

A.PlanningSourceVillageName,

A.ActualSourceVillageName

)

SELECT

LSA.Year,

LSA.WeekNum,

LSA.CompanyID,

LSA.CompanyName,

LSA.SiteName,

LSA.Longitude,

LSA.Latitude,

LSA.PropertyType,

LSA.SiteType,

LSA.SFType,

LSA.SiteIsChange,

LSA.EManufacturers,

LSA.ZConfiguration,

LSA.BBUNum,

LSA.RRUNum,

LSA.CSiteDate,

LSA.CCrenovationDate,

LSA.CTransmissionAccessDate,

LSA.CEquipmentInstallationDate,

LSA.CEngineeringOpeningDate,

LSA.CFullyOpenedDate,

LSA.CSsVerificationDate,

LSA.SortOrder,

LSA.LastModityID,

LSA.LastModityName,

LSA.LastModfiyTime,

LSA.MatingDesignDate,

LSA.MasterDeviceDesignDate,

LSA.RemarkOne,

LSA.RemarkTwo,

LSA.SubProjectName,

LSA.NewStationActualSiteName,

LSA.NewStationCompletedCablePipe,

LSA.NewTechnologyTypes,

LSA.PlanningSourceVillageName,

LSA.ActualSourceVillageName

FROM EPMSReport.dbo.FourGProjectInWeekReportTemp AS LSA

LEFT OUTER JOIN EPMSReport.dbo.FourGProjectInWeekReport A

ON LSA.SiteName = A.SiteName --要求是站点名称唯一

AND LSA.[year] = A.[year]

AND LSA.WeekNum = A.WeekNum

AND LSA.CompanyName = A.CompanyName

WHERE A.SiteName IS NULL

UPDATE A

SET

A.CompanyName = LSA.CompanyName,

A.SiteName =LSA.SiteName ,                                                                

A.Longitude=LSA.Longitude ,                                                                

A.Latitude=        LSA.Latitude ,        

A.PropertyType = LSA.PropertyType ,

A.SiteType = LSA.SiteType ,        

A.SFType =LSA.SFType ,

A.LastModityID=LSA.LastModityID,

A.LastModityName=LSA.LastModityName,

A.LastModfiyTime=LSA.LastModfiyTime                                                

FROM EPMSReport.dbo.FourGProjectInWeekReportTemp AS LSA

INNER JOIN         EPMSReport.dbo.FourGProjectInWeekReport AS A        

ON LSA.SiteName = A.SiteName --要求是站点名称唯一

AND LSA.[year] = A.[year]

AND LSA.WeekNum = A.WeekNum

AND LSA.CompanyName = A.CompanyName

WHERE A.SiteName IS NOT NULL

END

GO