谨慎使用update 最好对应字段 直接使用insert
DECLARE @pageIndex INT
DECLARE @pageSize INT
DECLARE @TotalpageIndex INT
declare @rows as int
SET @pageIndex = 1
SET @pageSize = 10000
set @TotalpageIndex=((select top 1 ID from [NewWeBusiness_PJM].[dbo].[Scale] order by ID desc)/@pageSize)+1
set @rows=1
WHILE @pageIndex<=@TotalpageIndex
BEGIN
insert into [Scale]([MiddleCode]
,[SmallCode]
,[AntiCode]
,[cxyCode]
,[CodeState]
,[DatCreate]
,[SelCount]
,[SelectDate]
,[exchangestat]
,[VerifyCode]
,[DataState]
,[Product]
,[OrderID]
,[DC]
,[CK]
,[RelationTime]
,LinkMidOrderNo
,IsLinkMid,LinkMidTime
,isOut,OutPDAUser,OutWay,UserName,OutTime,OutOrderNo
,IsInto,intoPDAUser,ProductNo,intoTime,intoOrderNo
)
SELECT s.[BigCode]
,s.[SmallCode]
,s.[AntiCode]
,[cxyCode]
,[CodeState]
,s.[DatCreate]
,[SelCount]
,[SelectDate]
,[exchangestat]
,[VerifyCode]
,[DataState]
,[Product]
,[OrderID]
,[DC]
,s.[CK]
,[RelationTime]
,s.OrderID
,(case when s.BigCode is null then 0 else 1 end) IsLinkMid, ISNULL( DATEDIFF(SECOND, '19700101 08:00:00',s.DatCreate),0) DatCreate
,(case when o.SmallCode is null then 0 else 1 end) isOut,'pm',1,o.Consignee,ISNULL( DATEDIFF(SECOND, '19700101 08:00:00',o.DatCreate),0) DatCreate,o.O_ID
,(case when i.SmallCode is null then 0 else 1 end) IsInto, 'pm',i.P_ID, ISNULL( DATEDIFF(SECOND, '19700101 08:00:00',i.DatCreate),0) DatCreate1,i.O_ID
FROM [NewWeBusiness_PJM].[dbo].[Scale] s WITH (NOLOCK)
left join [NewWeBusiness_PJM].[dbo].ScaleOutStoke o WITH (NOLOCK) on s.SmallCode=o.SmallCode and o.State='启用'
left join [NewWeBusiness_PJM].[dbo].ScaleInStoke i WITH (NOLOCK) on s.SmallCode=i.SmallCode
where s.ID <= (@pageSize*@pageIndex) and s.ID > (@pageSize*(@pageIndex-1))
SET @pageIndex = @pageIndex + 1
set @rows=@@ROWCOUNT
select @rows
END