INSERT INTO T_D_TA_WareHouseIn
(
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
看起来很麻烦