Excel 生成sql语句

CONCATENATE 拼接字符串   

IF 不能跟 CONCATENATE 连用，所以可以通过建立新列去完成更高的需求 

新建一列 cw
=CONCATENATE("insert into biCommenDetail (CreateID,CreateDate,ModifyID,ModifyDate,IsDeleted,DeptID,DataYear,DataMonth,ItemType,ItemName,ItemData
) values('1',GETDATE(),'1',GETDATE(),0,'4','2014','1','入库','",B3,"','",CS3,"')")

新建一列 cx
=CONCATENATE("insert into biCommenDetail (CreateID,CreateDate,ModifyID,ModifyDate,IsDeleted,DeptID,DataYear,DataMonth,ItemType,ItemName,ItemData
) values('1',GETDATE(),'1',GETDATE(),0,'4','2014','1','出库','",B3,"','",CT3+CU3,"')")

新建一列 cy
=CONCATENATE("insert into biCommenDetail (CreateID,CreateDate,ModifyID,ModifyDate,IsDeleted,DeptID,DataYear,DataMonth,ItemType,ItemName,ItemData
) values('1',GETDATE(),'1',GETDATE(),0,'4','2014','1','库存','",B3,"','",CV3,"')")

新建一列
=CONCATENATE(IF(CS3>0,CW3,""),IF(CT3+CU3>0,CX3,""),IF(CV3>0,CY3,""))

 

 

 

在备注列后面 新建三列(需要按照顺序)
第一列
=CONCATENATE("update biDept set SrcModifyDate = getDate(),Title='",RC[-15],"',Size='",RC[-14],"',Rent='",RC[-13],"',Period='",RC[-11],"',Landlord='",RC[-10],"',Shareholder='",IF(RC[1]<>"",MID(RC[1],1,LEN(RC[1])-1),""),"',Memo='",RC[-1],"' where ID = '",RC[-17],"'")
第二列
=CONCATENATE(IF(RC[-9]<>"",CONCATENATE(RC[-9],","),""),IF(RC[-8]<>"",CONCATENATE(RC[-8],","),""),IF(RC[-7]<>"",CONCATENATE(RC[-7],","),""),IF(RC[-6]<>"",CONCATENATE(RC[-6],","),""))
第三列
=IF(RC[-19]<>"",RC[-2],"")

第三列为最终的sql语句