把A中的数据全部插入到log表中,然后经过对数据的分解再全部插入B表中,这两个过程在一个事务里,没有用游标。现在的问题是B表中数据经常比log表中的数据要少,但我在测试的时候没有这样的问题,出了问题我又测了好几遍还是没问题。大家帮忙想一下有可能是什么原因引起的呢。
----------------------------------------------------------------
把A中的数据全部插入到log表中,然后经过对数据的分解再全部插入B表中?
后面那步没有必要啊?既然是全部插入,直接插入B表,然后在分析就行了.不必使用LOG这个中间表.
----------------------------------------------------------------
把A中的数据全部插入到log表中,然后经过对数据的分解再全部插入B表中?
后面那步没有必要啊?既然是全部插入,直接插入B表,然后在分析就行了.不必使用LOG这个中间表.
A 分解 -> B
--------------------
不了解分解过程如何,个人认为很可能分解时 NULL 值的问题。
===============================
就是把一个条形码分解出来,的确有些字段是空的,为什么空的会出问题呢
==============
清空的是A表不是log表,初的错误都是B表中数据比log表少,重复数据有些B表只插入了一条,有些都插进去了,另外有些数据只在log表中有B中没有,但这样的很少
Set ANSI_NULLS ---ON/OFF
Set ANSI_NULLS ---ON/OFF
——————————————————————————
没有,在往A表插数据的时候会产生大量的awaiting command,有影像么
===============================
就是把一个条形码分解出来,的确有些字段是空的,为什么空的会出问题呢--------------------------
那就用几条NULL值得记录分解试试,看看是不是这个问题,是这个问题再深入分解逻辑,不是就没必要了。
(
StockDate
,EntryCD
,VendorCD
,MakerItemCD
,Amount
,MakeDate
,BoxNo
,MakerLotNo
,SubPartsNo
,IndividualNo
,GaugeNo
,PartsCD
,StateFlag
,StateReason
,UpdateCD
,UpdateDate
)
SELECT
CONVERT(SMALLDATETIME,StockDate)AS StockDate
,EntryCD AS EntryCD
,CONVERT(INT,VendorCD)AS VendorCD
,RIGHT(LEFT(Maincd,16),14) AS MakerItemCD
,CONVERT(MONEY,RIGHT(LEFT(Maincd, 26),6))/100 AS Amount
,CASE WHEN ISDATE(RIGHT(LEFT(Maincd, 34),6))=0 THEN GETDATE() ELSE CONVERT(DATETIME,RIGHT(LEFT(Maincd, 34),6),120) END AS MakeDate ,CASE WHEN LEN(CASE WHEN RIGHT(LEFT(Maincd,36),2)='21' THEN RIGHT(RTRIM(Maincd),LEN(Maincd)-36) ELSE '' END)>14 THEN LEFT(CASE WHEN RIGHT(LEFT(Maincd,36),2)='21' THEN RIGHT(RTRIM(Maincd),LEN(Maincd)-36) ELSE '' END,14) ELSE CASE WHEN RIGHT(LEFT(Maincd,36),2)='21' THEN RIGHT(RTRIM(Maincd),LEN(Maincd)-36) ELSE '' END END AS BoxNo ,CASE WHEN LEN(case when left(SubCD,2)='10' then Substring(SubCD ,3,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -2) else case when charindex(' 10',SubCD,1)=0 then null else Substring(SubCD,charindex(' 10',SubCD,1)+3,case when charindex(' ',SubCD,charindex(' 10',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 10',SubCD,1)+1) end -(charindex(' 10',SubCD,1)+3))end end)>20
THEN LEFT(case when left(SubCD,2)='10' then Substring(SubCD ,3,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -2) else case when charindex(' 10',SubCD,1)=0 then null else Substring(SubCD,charindex(' 10',SubCD,1)+3,case when charindex(' ',SubCD,charindex(' 10',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 10',SubCD,1)+1) end -(charindex(' 10',SubCD,1)+3))end end,20)
ELSE case when left(SubCD,2)='10' then Substring(SubCD ,3,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -2) else case when charindex(' 10',SubCD,1)=0 then null else Substring(SubCD,charindex(' 10',SubCD,1)+3,case when charindex(' ',SubCD,charindex(' 10',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 10',SubCD,1)+1) end -(charindex(' 10',SubCD,1)+3))end end
END AS MakerLotNo ,CASE WHEN LEN(case when left(SubCD,4)='7002' then Substring(SubCD ,5,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -4) else case when charindex(' 7002',SubCD,1)=0 then null else Substring(SubCD,charindex(' 7002',SubCD,1)+5,case when charindex(' ',SubCD,charindex(' 7002',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 7002',SubCD,1)+1) end -(charindex(' 7002',SubCD,1)+5))end end)>30
THEN LEFT(case when left(SubCD,4)='7002' then Substring(SubCD ,5,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -4) else case when charindex(' 7002',SubCD,1)=0 then null else Substring(SubCD,charindex(' 7002',SubCD,1)+5,case when charindex(' ',SubCD,charindex(' 7002',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 7002',SubCD,1)+1) end -(charindex(' 7002',SubCD,1)+5))end end,30)
ELSE case when left(SubCD,4)='7002' then Substring(SubCD ,5,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -4) else case when charindex(' 7002',SubCD,1)=0 then null else Substring(SubCD,charindex(' 7002',SubCD,1)+5,case when charindex(' ',SubCD,charindex(' 7002',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 7002',SubCD,1)+1) end -(charindex(' 7002',SubCD,1)+5))end end
END AS SubPartsNo
,case when left(SubCD,3)='251' then Substring(SubCD ,4, 10) else case when charindex(' 251',SubCD,1)=0 then '0000000000' else Substring(SubCD,charindex(' 251',SubCD,1)+4,10) end end AS IndividualNo ,CASE WHEN LEN(case when left(SubCD,3)='240' then Substring(SubCD ,4,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -3) else case when charindex(' 240',SubCD,1)=0 then null else Substring(SubCD,charindex(' 240',SubCD,1)+4,case when charindex(' ',SubCD,charindex(' 240',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 240',SubCD,1)+1) end -(charindex(' 240',SubCD,1)+4))end end) > 30
THEN LEFT(case when left(SubCD,3)='240' then Substring(SubCD ,4,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -3) else case when charindex(' 240',SubCD,1)=0 then null else Substring(SubCD,charindex(' 240',SubCD,1)+4,case when charindex(' ',SubCD,charindex(' 240',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 240',SubCD,1)+1) end -(charindex(' 240',SubCD,1)+4))end end,30)
ELSE case when left(SubCD,3)='240' then Substring(SubCD ,4,case when CHARINDEX(' ',SubCD,1)=0 then len(SubCD) else CHARINDEX(' ',SubCD,1) end -3) else case when charindex(' 240',SubCD,1)=0 then null else Substring(SubCD,charindex(' 240',SubCD,1)+4,case when charindex(' ',SubCD,charindex(' 240',SubCD,1)+1)=0 then len(SubCD)+1 else charindex(' ',SubCD,charindex(' 240',SubCD,1)+1) end -(charindex(' 240',SubCD,1)+4))end end
END AS GaugeNo
,PartsCD AS PartsCD
,0 AS StateFlag
,'' AS StateReason
,CONVERT(decimal,@UpdateCD) AS UpdateCD
,GETDATE()AS UpdateDate
FROM T_W_TA_WareHouseIn
看起来很麻烦
1, 用Substring(Maincd,2,14) 代替RIGHT(LEFT(Maincd,16),14)
2, 使用coalesce代替繁琐的case嵌套
3, 定义字段时别偷懒,字段Maincd和SubCD定义的极不合理
不过你能不能先把数据导入,然后再UPDATE,在UPDATE过程再进行你的那些转换逻辑。
估计是在转换过程中丢掉数据的。