Wednesday, August 31, 2011

Creating SQL Insert Statements From An Existing Table In Excel


Let me explain the technique I derived to do this.

Step1: Get your table ready in the following format:


Step 2: Paste the following formula into the first cell just below the table (A8 here):

=CONCATENATE("INSERT INTO "&A$1&" ("&A$2&","&B$2&","&C$2&","&D$2&","&E$2&","&F$2&","&G$2&","&H$2&","&I$2&","&J$2&","&K$2&","&L$2&","&M$2&","&N$2&","&O$2&","&P$2&","&Q$2&","&R$2&","&S$2&","&T$2&","&U$2&","&V$2&","&W$2&","&X$2&","&Y$2&","&Z$2&","&AA$2&","&AB$2&","&AC$2&","&AD$2&","&AE$2&","&AF$2&","&AG$2&","&AH$2&","&AI$2&","&AJ$2&","&AK$2&","&AL$2&","&AM$2&","&AN$2&","&AO$2&","&AP$2&","&AQ$2&","&AR$2&","&AS$2&","&AT$2&","&AU$2&","&AV$2&","&AW$2&","&AX$2&","&AY$2&","&AZ$2&","&BA$2&","&BB$2&","&BC$2&","&BD$2&","&BE$2&","&BF$2&","&BG$2&","&BH$2&","&BI$2&","&BJ$2&","&BK$2&","&BL$2&","&BM$2&","&BN$2&","&BO$2&","&BP$2&","&BQ$2&","&BR$2&","&BS$2&","&BT$2&","&BU$2&","&BV$2&","&BW$2&","&BX$2&","&BY$2&","&BZ$2&","&CA$2&","&CB$2&","&CC$2&","&CD$2&","&CE$2&","&CF$2&","&CG$2&","&CH$2&","&CI$2&","&CJ$2&","&CK$2&","&CL$2&","&CM$2&","&CN$2&","&CO$2&","&CP$2&","&CQ$2&","&CR$2&","&CS$2&","&CT$2&","&CU$2&","&CV$2&","&CW$2&","&CX$2&","&CY$2&","&CZ$2&") VALUES ('"&TEXT(A3,"mm/dd/yyyy")&"','"&B3&"','"&C3&"','"&D3&"','"&E3&"','"&F3&"','"&G3&"','"&H3&"','"&I3&"','"&J3&"','"&K3&"','"&L3&"','"&M3&"','"&N3&"','"&O3&"','"&P3&"','"&Q3&"','"&R3&"','"&S3&"','"&T3&"','"&U3&"','"&V3&"','"&W3&"','"&X3&"','"&Y3&"','"&Z3&"','"&AA3&"','"&AB3&"','"&AC3&"','"&AD3&"','"&AE3&"','"&AF3&"','"&AG3&"','"&AH3&"','"&AI3&"','"&AJ3&"','"&AK3&"','"&AL3&"','"&AM3&"','"&AN3&"','"&AO3&"','"&AP3&"','"&AQ3&"','"&AR3&"','"&AS3&"','"&AT3&"','"&AU3&"','"&AV3&"','"&AW3&"','"&AX3&"','"&AY3&"','"&AZ3&"','"&BA3&"','"&BB3&"','"&BC3&"','"&BD3&"','"&BE3&"','"&BF3&"','"&BG3&"','"&BH3&"','"&BI3&"','"&BJ3&"','"&BK3&"','"&BL3&"','"&BM3&"','"&BN3&"','"&BO3&"','"&BP3&"','"&BQ3&"','"&BR3&"','"&BS3&"','"&BT3&"','"&BU3&"','"&BV3&"','"&BW3&"','"&BX3&"','"&BY3&"','"&BZ3&"','"&CA3&"','"&CB3&"','"&CC3&"','"&CD3&"','"&CE3&"','"&CF3&"','"&CG3&"','"&CH3&"','"&CI3&"','"&CJ3&"','"&CK3&"','"&CL3&"','"&CM3&"','"&CN3&"','"&CO3&"','"&CP3&"','"&CQ3&"','"&CR3&"','"&CS3&"','"&CT3&"','"&CU3&"','"&CV3&"','"&CW3&"','"&CX3&"','"&CY3&"','"&CZ3&"');")

I am pasting this whole formula so that it can be copied and reused in future.

Step 2: Shorten this formula by removing the unnecessary references, to make it like this:

=CONCATENATE("INSERT INTO "&A$1&" ("&A$2&","&B$2&","&C$2&","&D$2&") VALUES ('"&TEXT(A3,"mm/dd/yyyy")&"','"&B3&"','"&C3&"','"&D3&"');")

Step 3: Dealing with different data types:

a. For dates use TEXT(A3,"mm/dd/yyyy") instead of A3

b. For integers remove the single quotes. Second column is integer here, so the formula becomes:

=CONCATENATE("INSERT INTO "&A$1&" ("&A$2&","&B$2&","&C$2&","&D$2&") VALUES ('"&TEXT(A3,"mm/dd/yyyy")&"',"&B3&",'"&C3&"','"&D3&"');")

Notice the single quotes removed at both sides of B3.

Step 4: When data rows get added, simple drag down or copy the formula.

Step 5: Dealing with changes in columns:

a. If a column gets added, keep the column and data after the last column. Then add the column to both places in the formula. After adding column E, the formula becomes:

=CONCATENATE("INSERT INTO "&A$1&" ("&A$2&","&B$2&","&C$2&","&D$2&","&E$2&") VALUES ('"&TEXT(A3,"mm/dd/yyyy")&"',"&B3&",'"&C3&"','"&D3&"','"&E3&"');")

b. If a column gets removed, simple delete the column, and your formula gives error. Say, column C gets removed. Then your formula becomes:

=CONCATENATE("INSERT INTO "&A$1&" ("&A$2&","&#REF!&","&B$2&","&C$2&") VALUES ('"&TEXT(A3,"mm/dd/yyyy")&"','"&#REF!&"','"&B3&"','"&C3&"');")

In this case, you need to manually remove all &#REF!&"','" from the formula.