Generating SQL Inserts using Microsoft Excel

To boot strap any database we need to begin with some data to try out few queries, views, reports etc. Typing out each and every line of new Line query is difficult on either SSMS or MySql Workbench for its respective databases and it becomes even more mundane for to do that in cmd mode.
Yes accepted there are several tools both online and offline installable tools to generate a bunch of  intact 1000s SQL insert statements in a click. The offline ones are to be installed and web-ones are required to download the generated SQL, but much easier.

Data using Excel for DB

Excel / Spreadsheet software is something really excelling and it not at all wrong to quote it as poor man’s data warehouse, report base, data manager, data analyser etc. in fact any cool process oriented noun prefixed with data.
I personally feel to play with excel to generate the SQLs. There is a random number generator, mathematical function implementer, series filler. I use all the above things and make the required table structure which I want it as a table.

Generating SQL using Excel

“INSERT INTO EMPLOYEES VALUES ( ‘” & A1 & “‘,” & B2 & “)”
Make the contents/formula of the cell C1 as above statement and you will end up with a single statement. Similarly end up with 10 or 100 or 1000 of values as rows i.e generation of A-Column is straight forward and generation of the B column can be generated with same comfort like Employee1, Employee2, Employee3.

For the values like age, salary etc. you use either RAND() or RANDBETWEEN() function of excel and to fill / extend those values to other cells of the column, you just need to double click the pivot.

Steps to generate the SQL


I have used Microsoft Office 2013 – Excel. 

Step 1 : For name you can come up with a convention linke Student1, Customer1, Employee1 etc.


Step 2 : For values like age you the RANDBETWEEN()

Step 3 : Once you have extended the Columns for your values, use a string joint operation to build your SQL 

example for C1 cell  =”INSERT INTO EMPLOYEES VALUES ( ‘”&A1&”‘,”&B1&”)”





SampleApplication 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s