Tricks for generating SQL statements in Excel

Sometimes I use substitute to replace patterns in the SQL command instead of trying to build the sql command out of concatenation. Say the data is in Columns A & B. Insert a top row. In cell C1 place the SQL command using pattern:

insert into table t1 values('<<A>>', '<<B>>')

Then in rows 2 place the excel formula:

=SUBSTITUTE(SUBSTITUTE($C$1, "<<A>>", A2), "<<B>>", B2)

Note the use of absolute cell addressing $C$1 to get the pattern. Especially nice when working with char or varchar and having to mix the single and double quotes in the concatenation. Compare to:

=concatenate("insert into table t1 values '", A2, "', '", B2, "')"

An other thing that has bitten me more than once is trying to use excel to process some chars or varchars that are numeric, except they have leading zeros such as 007. Excel will convert to the number 7.

The semi-colon needs to be inside the last double quote with a closing paren. When adding single quotes around a string, remember to add them outside your selected cell.

(spaces added for visibility - remove before inserting)

=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")

Here is another view:

=CONCATENATE("insert into table (id, date, price) values (",C3,",'",D3,"',",B3,");")