Sql存储过程将数据拆分后存入新表
CREATE PROC CostItemsInto_KYPM_Project_FeeRecord(@workid nvarchar(10))ASBEGIN declare @RecordID nvarchar(10)--FeeRecord表记录ID declare @ProjectID nvarchar(10)--合同ID declare @FeeItem nvarchar(10)--费用项 declare @RealAmount numeric(15,2)--金额 declare @RealDate datetime--审批通过时间 declare @Summary nvarchar(200)--摘要 declare @Remark nvarchar(200)--备注 declare @FeeItemName nvarchar(200)--费用类型 declare @i int -- `符号出现的总数，用于判断记录条数 declare @count int -- 用来记录条数 declare @countStart int -- 用来记录开始位置 set @count=0 set @countStart=0 declare cur cursor for select len(Cast(a.DATA_7 as varchar(8000)))-len(replace(Cast(a.DATA_7 as varchar(8000)),'`','')) as i, SUBSTRING(a.DATA_12,CHARINDEX('⊙',a.DATA_12) 1,LEN(a.DATA_12)) as projectid, b.LastUpdateTime from Form_Table_099 a left join (select * from Form_Work where DeleteMark=0 and Status=2)b on a.WorkID=a.WorkID where a.WorkID=@workid open cur fetch next from cur into @i,@ProjectID,@RealDate --ProjextID set @ProjectID = 'C_' @ProjectID while @i>@countStart begin --RecordID set @RecordID = '01' RIGHT('00000000' cast((cast(RIGHT((select isnull(max(RecordID),0) from KYPM_Project_FeeRecord),8) as int) 1) as nvarchar(8)), 8)--记录ID update Sys_IdentityValues set ItemValue= (cast(RIGHT((select isnull(max(RecordID),0) from KYPM_Project_FeeRecord),8) as int) 1) --更新给号库 where (ItemKey = 'KYPM_Project_FeeRecord') --FeeItemName select @FeeItemName=SUBSTRING( Cast(DATA_7 as varchar(8000)), dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 1) 1, dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 2)-dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 1)-1 ) from Form_Table_099 where WorkID=@workid --FeeItem select @FeeItem=BudgetCode from KYPM_Project_BudgetTemplateInfo where TemplateID='0100000001' and BudgetName=@FeeItemName --RealAmount select @RealAmount= Cast( SUBSTRING( Cast(DATA_7 as varchar(8000)), dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 4) 1, dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 5)-dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 4)-1 )as numeric(15,2) ) from Form_Table_099 where WorkID=@workid --Summary select @Summary=SUBSTRING( Cast(DATA_7 as varchar(8000)), dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 2) 1, dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 3)-dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 2)-1 ) from Form_Table_099 where WorkID=@workid --Remark select @Remark=SUBSTRING( Cast(DATA_7 as varchar(8000)), dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 5) 1, dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 6)-dbo.selectCharLen(Cast(DATA_7 as varchar(8000)),'`',@countStart 5)-1 ) from Form_Table_099 where WorkID=@workid insert into KYPM_Project_FeeRecord (RecordID,ProjectID,FeeItem,RealAmount,RealDate,Summary,Remark,DeleteMark,STATUS) values(@RecordID,@ProjectID,@FeeItem,@RealAmount,@RealDate,@Summary,@Remark,0,1) set @count=@count 1 set @countStart=9*@count end close cur deallocate curEND
赞 (0)