Sql存储过程将数据拆分后存入新表
CREATE PROC CostItemsInto_KYPM_Project_FeeRecord(@workid nvarchar(10)) AS BEGIN declare @RecordID nvarchar(10) declare @ProjectID nvarchar(10) 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 cur END