Big Data

How to use SQL to create more SQL

SQL is regularly used to manipulated data in a database. Here's a technique that can make manipulation of multitudes of data easier.

hero
Image: iStockphoto.com/Rafomundo

SQL is commonly used by database professionals to search and manipulate corporate data. By "manipulating corporate data," I mean updating data that was incorrect—not any nefarious "cooking the books" stuff. In addition to retrieving and manipulating data, SQL can also be used to create more SQL when necessary. Allow me to share a way to use SQL to create SQL that will aid in updating, inserting, or deleting data.

Scenario presented by users

Tenured sales department commission rates have been scheduled to increase in Q3 of this year. The rates will go from one percent to two percent. These rates will need to be set up with an effective date of July 1, 2016. The old rates will need to be stored in history and expired as of the end of Q2.

Sounds easy enough, right? Updating the commission records can be completed with an "update" SQL statement and "insert" statement as shown below:

NOTE: This syntax is run on the data structure of an Informix database. Oracle, MSSQL and other database management systems may vary slightly in syntax.

UPDATE sales_pay

SET exp_date = '06/30/2016'

WHERE comm_rate = '0.01'

AND employee_id = 'TJONES512'';

INSERT INTO sales_pay VALUES ('TJONES512','0.02','07/01/2016','12/31/9999');

This script is easy to write up as it's only for one employee id, noted as "TJONES512." Unfortunately, about 200 other sales department members are qualified for the commission increase. The SQL statement can be rewritten another 200 times or even copied and pasted with a search/replace on the employee_id. This is tedious to say the least. Here's how you can create the script using SQL.

SEE: A new breed of database hopes to blend the best of NoSQL and RDBMS

Create a temporary table of the new dataset

Gather a list of all the applicable staff affected by the new commision rate. I'm sure your user can compile a quick spreadsheet for you. Create a text file from that spreadsheet to load into a temporary table in your database. In this instance, I'll use a spreadsheet with column A utilized as employee_id; column B as comm_rate; column C as eff_date; column D as exp_date.

loadfile.jpg
Now save the file in text tab-delimited format. This will be the file you load into your temp table, so be sure to notate the path where your file is stored.
saveastxt.jpg

In the SQL editor connected to your database, create a temp table called new_comm_tmp with the appropriate field names and data format using the script below:

CREATE TEMP TABLE new_comm_tmp (empl_id VARCHAR (10), comm_rate DECIMAL, eff_date DATE, exp_date DATE) WITH NO LOG;

LOAD FROM "C:\<the path of your text file>\comm_load.txt" DELIMITER " "

—your file had tabs as a delimiter

INSERT INTO new_comm_tmp;

SELECT * FROM new_comm_tmp; —verifying the data load into the temp table

Manipulate or load data into the existing database table

This is where the fun begins. The cool thing about SQL is you can use a "select" statement to generate a string of text as long as it's enclosed in quotation marks. So the first thing to do is expire the existing records in the database as requested by the user. This means writing an "update" script, but this time we'll generate our multiple "update" script with a "select" statement.

SELECT "UPDATE sales_pay SET exp_date = '06/30/2016' WHERE empl_id = '"||TRIM(empl_id)||"' AND comm_rate = '0.01';"

FROM new_comm_tmp;

When you run the above statement, you will get an "update" statement that will include only the employee IDs mentioned in your temp table. This strategy works because the fundamental structure of an SQL statement is to SELECT data FROM a data source WHERE your specified parameters are met. Above is a "select" statement that has a text string specifying what's to be returned from the temp table you created.

Output:

UPDATE sales_pay SET exp_date = '06/30/2016' WHERE empl_id = 'TJONES512' AND comm_rate = '0.01';

UPDATE sales_pay SET exp_date = '06/30/2016' WHERE empl_id = 'BGATES588' AND comm_rate = '0.01';

UPDATE sales_pay SET exp_date = '06/30/2016' WHERE empl_id = 'SJOBS144' AND comm_rate = '0.01';

UPDATE sales_pay SET exp_date = '06/30/2016' WHERE empl_id = 'APRUITT414' AND comm_rate = '0.01';

UPDATE sales_pay SET exp_date = '06/30/2016' WHERE empl_id = 'DVADER077' AND comm_rate = '0.01';

UPDATE sales_pay SET exp_date = '06/30/2016' WHERE empl_id = 'LTORVALDS111' AND comm_rate = '0.01';

Those "update" statements can all be copied and pasted into your SQL editor and executed. Now that the old commission rates have been set to expire, you can insert the new records into the database as requested. Here's one way you create a group of "insert" statements using the SQL to create SQL:

SELECT "INSERT INTO sales_pay VALUES ('"||TRIM(empl_id)||"', '"||TRIM(comm_rate)||"','"||(eff_date)||"','"||(exp_date)||"');"

FROM new_comm_tmp

The above would work in this scenario, but an even easier way would be to just run:

INSERT INTO sales_pay (SELECT * from new_comm_tmp)

This works because the table structure of the temp table is the same as the database table at hand.

How can data be deleted?

With the temp table as the basis of the proposed data deletion, you can craft a script the same way as before with a "select" statement. If the user requests old data be deleted, it's an easy task. Here's how:

SELECT "DELETE FROM sales_pay WHERE empl_id = '"||TRIM(empl_id)||"' and comm_rate = '0.01';"

FROM new_comm_tmp;

This will be the output:

DELETE FROM sales_pay WHERE empl_id = 'TJONES512' and comm_rate = '0.01';

DELETE FROM sales_pay WHERE empl_id = 'BGATES588' and comm_rate = '0.01';

DELETE FROM sales_pay WHERE empl_id = 'SJOBS144' and comm_rate = '0.01';

DELETE FROM sales_pay WHERE empl_id = 'APRUITT414' and comm_rate = '0.01';

DELETE FROM sales_pay WHERE empl_id = 'BGATES588' and comm_rate = '0.01';

DELETE FROM sales_pay WHERE empl_id = 'DVADER077' and comm_rate = '0.01';

DELETE FROM sales_pay WHERE empl_id = 'LTORVALDS111' and comm_rate = '0.01';

Copy the "delete" statements and paste them into your SQL editor and execute them. It's as easy as that, but of course make sure you have the proper database permissions to alter table data via SQL based on your standard operating procedures. When you've completed your tasks, remove your temp table from the database or your DBA will come looking for you. Just run this:

DROP TABLE new_comm_tmp;

Also read...

Other tips?

What SQL tips do you use day-to-day to manipulate data? Like most things involving computers systems, there are multiple ways to do a single task. What works for you? Share your advice with fellow TechRepublic members.

About Ant Pruitt

Ant Pruitt is an IT Support Professional with a passion for showing the non-geek how great technology can be. He writes for a variety of tech publications and hosts his own podcast. Ant is also an avid photographer and weight lifter.

Editor's Picks

Free Newsletters, In your Inbox