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
- =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.
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.
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...);
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?
