Use Excel to Write SQL Queries

Microsoft Excel is a handy tool for creating text with repeating patterns. It is the only common tool I can think of that easily combines algebraic and text operations. For example, today I created a set of INSERT statements as follows. In column 1, I created a series of numbers from 6 to 49 using the formula

=A1+1

Then, in the second column,

="INSERT INTO `slots` SET `eventid`='2', `jobid`=" & A1 & ", `slots`='2'"

The result looks like this:

Excel screenshot

Now it’s an easy copy/paste into phpMyAdmin‘s SQL query window, and the task is complete. I find this a lot faster than writing a short script to accomplish the same task.

3 comments

  1. [removed due to presence of advertising] says:

    Can you tell me how long did it take you to complete this task?! What makes you think that it is much faster than the writing of the script?!

  2. rkassissieh says:

    Let’s see. About a minute? I was able to copy and paste the results instead of having to write a new script and capture its output somewhere.

  3. Amber Hall says:

    Excellent. I have users who have not been formally trained in SQL. I am doing something similar but using columns to hold selectabl lists. For example Column A list: SELECT, SELECT DISTINCT, CREATE TABLE, CREATE USER, INSERT INTO, etc. The Column B will hold a list of column names. The Column C next another List of conditions such as FROM, WHERE. This continues out to complete. The final column will Concatenate the text from all other column to write the script that can be cut and pasted into SQL.