Microsoft Excel is a great product. You can use it to do the simple tasks and even create a game. It is one of the most used product in IT Industry for project management and tracking.
Here I will share simple tip to generate repeating SQL statement using Excel.
Case 1:- Create a table DROP script
Assume a random set of data which contains table information like table name, owner, and other information. You want to drop all the tables or selected table. You can always write a logic using PL/SQL and execute immediate statement or you can also use any tool like NOTEPAD++ and use copy and paste to create commands.
How to achieve this using less effort?
Drop Command in Oracle Database
DROP TABLE table_name;
Open excel which has table information
- select E2 cell
- Write a function as shown below.
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.
CONCATENATE(text1, [text2], …)
- =CONCATENATE(“Hello “, A2, ” How are you “)
A2 – is replaced with cell data. Assume A2 – Tom. It will show Hello Tom How are you.
|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.|
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 may find this helpful. Do you have any such small trick? Do share in the comments section below?