Excel 批量生成insert 语句

爱是永恒2023

已于 2023-06-09 14:52:28 修改

阅读量568

点赞数

文章标签： excel

于 2023-04-10 17:36:24 首次发布

本文链接：https://blog.csdn.net/weixin_46942018/article/details/130065662

版权

1.日期函数需要用：TEXT(AB2,"yyyy-MM-dd") 特殊处理（数字显示日期）

="INSERT INTO TabName VALUES('"&B2&"','"&C2&"','"&D2&"','"&E2&"','"&TEXT(F2,"yyyy-MM-dd")&"',"&G2&","&H2&",'','','',NULL,'"&M2&"','',0,NULL,0,NULL,"&S2&",'"&T2&"','"&U2&"','"&V2&"','"&TEXT(W2,"yyyy-MM-dd HH:mm:ss")&"',1,2,'"&Z2&"',"&AA2&",'"&TEXT(AB2,"yyyy-MM-dd")&"','"&AC2&"',"&AD2&",'',"&AF2&",'"&AG2&"')"


2.数据库批量修改某一列的值
update TabName set OperationResult=CONCAT(CONVERT(varchar(100),CAST(OperationResult as decimal(9,2))*100),'%')
where ProjectID=2
and ISNULL(OperationResult,'')<>''
and OperationResult is not null
and OperationResult='NULL'



3.update TabName set OperationResult=REPLACE(OperationResult,'.00%','%') where Id=1150