print '当前表:IWT_Page'
declare @max_id int --获得最大的
select @max_id=ISNULL(max(time_stamp),0) from [Event_topic]
print @max_id
;with cet_tb
as
(
SELECT [TopicId]
,[TopicTitle]
,[TopicCrt]
,[releaseMedia]
,[EventType]
,[area]
,[EventSchedule]
,[BroadcastingType]
,[cityName]
,[productName]
,time_stamp
--
FROM [C1].[iWOMTrackData].[dbo].[IWT_TopicTracking]
where 1=1
and BrandId=20002
and TopicState=1
and time_stamp>@max_id
)
merge into [Haier_Charts].[dbo].[Event_topic] as T
using cet_tb as S
on T.TopicId=S.TopicId
when matched
then update
SET T.[TopicID] = S.[TopicId]
,T.[TopicName] = S.[TopicTitle]
,T.[TopicDate] = S.[TopicCrt]
,T.[mediaName] = S.[releaseMedia]
,T.[EventType] = S.[EventType]
,T.[AreaName] = S.[area]
,T.[EventSchedule] =S.[EventSchedule]
,T.[BroadcastingType] =S. [BroadcastingType]
,T.[cityName] = S.[cityName]
,T.[productName] = S.[productName]
,T.time_stamp = S.time_stamp
when not matched
then insert values(S.[TopicId],S.[TopicTitle],S.[TopicCrt],S.[releaseMedia],S.[EventType],S.[area],S.[EventSchedule],S. [BroadcastingType],S.[cityName],S.[productName],S.time_stamp);
(备注:时间戳类型可以与BIGINT类型之间进行转换!)
删除 操作建议通过标示位,代替然后再统一进行物理删除
可以用一个表配置,每一个表物理删除的时间
表结构:
表名、时间、状态