Generate SQL from Excel

Use below clause in Excel to generate insert sqls to import data in Excel into SQL server.

 

Tips:   SUBSTITUTE(D4,"'","''")---if D4 contain ', this function will convert ' to ''

             Using "," to connect template strings

             Replace the {InputFileName} to the real excel name

 

=CONCATENATE("insert into DM_TT_INDIV_ADDITIONAL_INFO(INPUT_FILE_NAME,DM_LOAD_TIMESTAMP,REMARK_TEXT,PROCESS_STATUS,PROCESS_DETAIL,DM_PROCESS_TIMESTAMP,te_itfn,te_rtntype,","te_ntnl_code,te_dob_date,te_birth_cntry_code,te_gender_code,te_prfr_cntc_code,te_dual_ctzn_in_local,te_bsname,te_bsns_actvy_code,te_bs_reg_no,te_boi_in,","te_boi_stt_date,te_boi_end_date)values ('{InputFileName}','",TEXT(NOW(),"mm/dd/yyy") &TEXT(NOW()," h:mm:ss.000"),"','','Loaded','','','",SUBSTITUTE(B4,"'","''"),"','",SUBSTITUTE(C4,"'","''"),"','SRI LANKA','",IF(LEN(TRIM(E4))>0,TEXT(E4,"mm/dd/yyyy"),""),"','",SUBSTITUTE(F4,"'","''"),"','",SUBSTITUTE(G4,"'","''"),"','",SUBSTITUTE(H4,"'","''"),"','",SUBSTITUTE(I4,"'","''"),"','",SUBSTITUTE(J4,"'","''"),"','",SUBSTITUTE(K4,"'","''"),"','",SUBSTITUTE(L4,"'","''"),"','",SUBSTITUTE(M4,"'","''"),"','",IF(LEN(TRIM(N4))>0,TEXT(N4,"mm/dd/yyyy"),""),"','",IF(LEN(TRIM(O4))>0,TEXT(O4,"mm/dd/yyyy"),""),"');")