How to use Excel Spreadsheet to generate SQL statements

General / By
Excel Spreadsheet to generate sql statement

This article explains how to use Excel to generate SQL statements like drop table, insert and update easily using built-in CONCATENATE excel function.

Microsoft Excel is one of the most used product in the IT Industry for project management and tracking. It is a great product. You can use it to do simple tasks and even create a game. Here I will share a simple tip to generate repeating SQL statement using Excel. 

Before going further let’s discuss concatenate function in Excel.

CONCATENATE

It is built-in excel function to concatenate multiple strings into on single sting.
 For example:

  • =CONCATENATE(“Hello “, A2, ” How are you “)

A2 – is replaced with cell data. Assume A2 – Tom. It will show Hello Tom How are you.

Argument name Description
text1    (required) The first item to join. The item can be a text value, number, or cell reference.
Text2, …    (optional) Additional text items to join. You can have up to 255 items, up to a total of 8,192 characters.

Drop statement using Excel

Let’s assume a random set of data which contains table information like table name, owner, and other information. Now you want to drop all or all the selected tables. One way is to write a logic using PL/SQL and use execute immediate statement. You can also use a text editor like Notepad++ and copy and paste to create commands.

How to achieve this using less effort?

Drop table syntax

DROP TABLE table_name;

1. Open excel which has table information as shown below.

sample dummy table data

2. Select E2 or any black cell.

3. Write an excel concatenate function as shown below.

=CONCATENATE("DROP TABLE",A2,".",B2)

4. Copy and paste or click and drag this formula to all cells.

That’s it and your script is ready.

enter excel formula

You can use the same method to generate more complex scripts for insert, update and delete.

Insert statement using Excel

Using the same approach you can create insert statement as shown in below image.

Insert statement syntax

INSERT INTO table_name(column_name....) values (values...);

excel to generate insert statement

Check the image to create the excel sheet and use formula.

= CONCATENATE(A2,B2,",",C2,D2)

Below is generate insert clause

INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);
INSERT INTO dummy_insert(dummy_1,dummy_2) values (1,2);

Summary

Not a great Tip, but this has helped me a lot during Inventory Support, where I need to apply multiple mundane fixes daily.

I hope you find this helpful. Do you have any such small trick? Do share in the comments section below?

 

Related Posts