金蝶K3--BOM表--自定义格式导入

use hrhost		-->aiserp2019.dbo.
go

--truncate table aiserp2019.dbo.icbom
--truncate table aiserp2019.dbo.icbomchild
--update aiserp2019.dbo.t_billcodeby set fnummax=2 where fbilltypeid=50 and fformatchar='bomb1903'
--update aiserp2019.dbo.icmaxnum set fmaxnum=1002 where ftablename='icbom'

if not exists(select 1 from sys.tables where name='bom_import_log')
begin
	create table bom_import_log (id int identity(100000,1) not null,fimportant varchar(100),fcontent varchar(max),flastdatetime datetime)
end
go

declare @fcolumn_name varchar(max)
select @fcolumn_name=name from sys.columns where object_id('bom_import_file')=object_id and column_id=1
set @fcolumn_name = 'bom_import_file.' + @fcolumn_name	--替换第一列名称
if exists(select * from information_schema.columns where table_name='bom_import_file' and ordinal_position=1 and column_name<>'f1')
begin
	print @fcolumn_name
	exec sp_rename @fcolumn_name,'f1'
end
if exists(select * from tempdb..sysobjects where name like '#bom_import_file%')
begin
	print 'drop table #bom_import_file'
	drop table #bom_import_file	--建立临时表
end
go

create table #bom_import_file (id int identity(101,1) not null,fsort varchar(20),fparentid int,fitemid int,fnumber varchar(30),fname varchar(30),fmodel varchar(100),funitid int,funit_name varchar(20),fuseqty decimal(18,10),floss_rate decimal(18,10),fusetype varchar(30),fdefaultloc int,fdefaultloc_name varchar(16))

go

declare @f1 varchar(30),@f2 varchar(30),@f3 varchar(30),@f4 varchar(30),@f5 varchar(30),@f6 decimal(18,10),@f7 decimal(18,10),@f8 decimal(18,10),@f9 decimal(18,10),@f10 varchar(10),@f11 varchar(20)
declare @fid int,@icount int
set @fid = 1
select @icount=count(f3) from bom_import_file
declare my_select cursor for select f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11 from bom_import_file	--写入临时表数据
open my_select
fetch next from my_select into @f1,@f2,@f3,@f4,@f5,@f6,@f7,@f8,@f9,@f10,@f11
while @@fetch_status=0
begin
	if @fid=1
	begin
		insert into #bom_import_file (fparentid,fnumber,funit_name,fuseqty)values(0,@f3,'