-- 以第二个任务为例。既然是永久表应该先建好了 T_SS2 DECLARE @count int DECLARE @lastEndSID int DECLARE @startSID int DECLARE @endSID int DECLARE @batchCount int DECLARE @batchSID intSELECT @count=MAX(SID) FROM T_SS SELECT @lastEndSID=MAX(SID) FROM T_SS2IF @lastEndSID IS NULL SET @startSID = (@count/3) + 1 ELSE SET @startSID = @lastEndSID + 1SET @endSID = (@count/3)*2 SET @batchCount = 100 SET @batchSID = @startSID WHILE @batchSID <= @endSID BEGIN IF (@endSID - @batchSID + 1) < @batchCount SET @batchCount = @endSID - @batchSID + 1 INSERT INTO T_SS2(id,utime,SID) SELECT TOP (@batchCount) id,utime,SID FROM T_SS WHERE SID >= @batchSID SET @batchSID = @batchSID + @batchCount END
-- 以第二个任务为例。既然是永久表应该先建好了 T_SS2 DECLARE @count int DECLARE @lastEndSID int DECLARE @startSID int DECLARE @endSID int DECLARE @batchCount int DECLARE @batchSID intSELECT @count=MAX(SID) FROM T_SS SELECT @lastEndSID=MAX(SID) FROM T_SS2IF @lastEndSID IS NULL SET @startSID = (@count/3) + 1 ELSE SET @startSID = @lastEndSID + 1SET @endSID = (@count/3)*2 SET @batchCount = 100 SET @batchSID = @startSID WHILE @batchSID <= @endSID BEGIN IF (@endSID - @batchSID + 1) < @batchCount SET @batchCount = @endSID - @batchSID + 1 INSERT INTO T_SS2(id,utime,SID) SELECT TOP (@batchCount) id,utime,SID FROM T_SS WHERE SID >= @batchSID SET @batchSID = @batchSID + @batchCount END
求 @count 是全表计数。
SELECT ... INTO 的时候又分了组,而且 SID 居然还是 1..@count 的连续自然数?
@count 是全表计数,把表分成三份啊,分组是因为有HAVING,T_SS 是自增字段,我也感觉哪有问题,就是不知道
@count 是全表计数,把表分成三份啊,分组是因为有HAVING,T_SS 是自增字段,我也感觉哪有问题,就是不知道
T_SS 的SID是自增字段
utime--这是时间有大小关系列?
同步时只需要记录同步时间,每次开始同步时把同步时间记录下来,下次同步时条件utime>上次同步时间
DECLARE @count int
DECLARE @lastEndSID int
DECLARE @startSID int
DECLARE @endSID int
DECLARE @batchCount int
DECLARE @batchSID intSELECT @count=MAX(SID) FROM T_SS
SELECT @lastEndSID=MAX(SID) FROM T_SS2IF @lastEndSID IS NULL
SET @startSID = (@count/3) + 1
ELSE
SET @startSID = @lastEndSID + 1SET @endSID = (@count/3)*2
SET @batchCount = 100
SET @batchSID = @startSID
WHILE @batchSID <= @endSID
BEGIN
IF (@endSID - @batchSID + 1) < @batchCount
SET @batchCount = @endSID - @batchSID + 1 INSERT INTO T_SS2(id,utime,SID)
SELECT TOP (@batchCount) id,utime,SID
FROM T_SS
WHERE SID >= @batchSID SET @batchSID = @batchSID + @batchCount
END
DECLARE @count int
DECLARE @lastEndSID int
DECLARE @startSID int
DECLARE @endSID int
DECLARE @batchCount int
DECLARE @batchSID intSELECT @count=MAX(SID) FROM T_SS
SELECT @lastEndSID=MAX(SID) FROM T_SS2IF @lastEndSID IS NULL
SET @startSID = (@count/3) + 1
ELSE
SET @startSID = @lastEndSID + 1SET @endSID = (@count/3)*2
SET @batchCount = 100
SET @batchSID = @startSID
WHILE @batchSID <= @endSID
BEGIN
IF (@endSID - @batchSID + 1) < @batchCount
SET @batchCount = @endSID - @batchSID + 1 INSERT INTO T_SS2(id,utime,SID)
SELECT TOP (@batchCount) id,utime,SID
FROM T_SS
WHERE SID >= @batchSID SET @batchSID = @batchSID + @batchCount
END
2、任务二是从 (@count/3) + 1 开始的啊!
那,我分三个作业一起进行不就会有三个sid=1的记录吗,两个表的sid不是对应不上了吗,还有数据跑的比1/3还多,是怎么回事
我#12不是举例了。不要的自己删除。
只要你自己控制好@startSID、@endSID 的计算公式,就算一开始只有1条记录,只会归到第一个作业中。
如果都包含了记录1,肯定是后两个作业的 @startSID 算错了。