Friday, 18 August 2017
Technical

How to use Excel Spreadsheet to generate SQL statements

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

sample dummy table data

 

  1. select E2 cell
  2. Write a function as shown below.

=CONCATENATE(“DROP TABLE”,A2,”.”,B2)

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.

Reference:

CONCATENATE(text1, [text2], …)

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.

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?

 

Leave a Reply