EXCEL 数据直接复制单元格格式如下:
希望形成的SQL语句为:
insert into zt_investCompDetail(zyear,zmonth,zid,thisyearvalue,endvalue) values('2018','6','1','40255.00','40231.00') ;
insert into zt_investCompDetail(zyear,zmonth,zid,thisyearvalue,endvalue) values('2018','6','2','25905.00','26737.00') ;
insert into zt_investCompDetail(zyear,zmonth,zid,thisyearvalue,endvalue) values('2018','6','3','9100.00','11578.00') ;
insert into zt_investCompDetail(zyear,zmonth,zid,thisyearvalue,endvalue) values('2018','6','4','41079.00','46111.00') ;
最终编写存储过程解决:
----------------------------------------------------------------------------------------
--方法2 只执行存储过程 zpro_investComp_big ,传递EXCEL拷贝来的字符串参数
----------------------------------------------------------------------------------------
--select * from zt_investCompDetail where zyear=2018 and zmonth in (6)
--delete from zt_investCompDetail where zyear=2018 and zmonth in (6)
--备注红色字符串部分直接从EXCEL上复制粘贴过来
exec zpro_investComp_big N'
2018 6 CP01 公司债 40255.00 40231.00
2018 6 CP02 股权投资 25905.00 26737.00
2018 6 CP03 基金 9100.00 11578.00
2018 6 CP04 股票 41079.00 46111.00
'
--构造存储过程
drop proc zpro_investComp_big
go
create proc zpro_investComp_big
(
@str_init varchar(max)
)
as
begin
Declare @str varchar(max)
set @str = @str_init
select @str = REPLACE(@str, CHAR(13), ' ')
select @str = REPLACE(@str, CHAR(10), ' ')
select @str = REPLACE(@str, CHAR(9), ' ')
select @str = REPLACE(@str, '公司债', ' ')
select @str = REPLACE(@str, '股权投资', ' ')
select @str = REPLACE(@str, '基金', ' ')
select @str = REPLACE(@str, '股票', ' ')
--构造临时表
drop table ztemp_ins
SELECT IDENTITY(int,1,1) as zid,itg_str INTO ztemp_ins FROM (
select itg_str FROM DBO.[itg_Split](@str,' ') where itg_str <> '' and itg_str is not null
) as zt
--更新类别字段
update ztemp_ins set itg_str=SUBSTRING(itg_str,4,1)
where itg_str like '%CP%'
and len(itg_str)>1
--select * from ztemp_ins
--变量循环4行
declare @n int,@rows int
set @n = 1
set @rows = 20
declare @str_ins varchar(1000)
while @n<=@rows
begin
set @str_ins = ''
SELECT @str_ins =
STUFF(
(SELECT char(39) + ',' + char(39) + cast(itg_str as varchar(200))
FROM ztemp_ins
where zid between @n and @n+4
order by zid asc
FOR xml path('')
),1,2,''
)
set @str_ins = 'insert into zt_investCompDetail(zyear,zmonth,zid,thisyearvalue,endvalue) values(' +@str_ins + char(39) + ') ;'
print (@str_ins)
exec (@str_ins)
select @n=@n+5
end
print('INSERT 语句已全部执行完毕')
exec zpro_investComp
print('已更新当月统计')
end
go
另有方法1,在EXCEL上形成SQL语句
----------------------------------------------------------------------------------------
--方法1:EXCEL 中 G列单元格补充公式,形成SQL语句
----------------------------------------------------------------------------------------
Step1.1:EXCEL 中 G列单元格补充公式:
=CONCATENATE("insert into zt_investCompDetail(zyear,zmonth,zid,thisyearvalue,endvalue) values('",A22,"','",B22,"','",RIGHT(C22,1),"','",D22,"','",E22,"','",F22,"');")