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

 

来源：https://www.icode9.com/content-2-752501.html