Using temporary tables in SQL

SQL

In the world of SQL (Structured Query Language), managing and transforming data efficiently is crucial for any database professional or developer. One valuable tool that often goes underutilized but can significantly simplify complex data operations is the concept of temporary tables.

Temporary tables are a versatile feature in SQL databases that allow you to store and manipulate data temporarily within a session. They provide a powerful and structured way to work with data, making it easier to handle complex tasks, analyze data, and improve query performance.

Using temporary tables in SQL can offer several advantages over direct inserting when working with medium-sized datasets or complex operations. Here are some benefits of using temporary tables:

1. Improved Performance
2. Reduced Locking and Blocking
3. Easier Debugging and Testing
4. Enhanced Reusability
5. Transaction Isolation

However, it’s important to note that the use of temporary tables should be appropriate for your specific use case. They may not be suitable for very large datasets where memory or disk space

Let's demonstrate its usage with an example

We have a table named “Sales”

CREATE TABLE results (
    individual_number INT PRIMARY KEY,
    bmi INT,
    score INT,
    message VARCHAR(255),
    updated_at TIMESTAMP,
);

Here we are about to update thousands of rows in it.

-- Update the row where individual_number = 1 and updated_at = '2023-10-05 08:00:00'
UPDATE results
SET
    bmi = 5,
    score = 5,
    message = 'one'
WHERE
    individual_number = 1
    AND updated_at = '2023-10-05 08:00:00';

-- Update the row where individual_number = 2 and updated_at = '2023-10-05 08:30:00'
UPDATE results
SET
    bmi = 6,
    score = 3,
    message = 'two'
WHERE
    individual_number = 2
    AND updated_at = '2023-10-05 08:30:00';

-- Update the row where individual_number = 3 and updated_at = '2023-10-05 09:15:00'
UPDATE results
SET
    bmi = 7,
    score = 2,
    message = 'three'
WHERE
    individual_number = 3
    AND updated_at = '2023-10-05 09:15:00';

-- Update the row where individual_number = 4 and updated_at = '2023-10-05 10:30:00'
UPDATE results
SET
    bmi = 8,
    score = 1,
    message = 'four'
WHERE
    individual_number = 4
    AND updated_at = '2023-10-05 10:30:00';

-- Update the row where individual_number = 5 and updated_at = '2023-10-05 11:00:00'
UPDATE results
SET
    bmi = 9,
    score = 4,
    message = 'five'
WHERE
    individual_number = 5
    AND updated_at = '2023-10-05 11:00:00';

-- Update the row where individual_number = 6 and updated_at = '2023-10-05 12:15:00'
UPDATE results
SET
    bmi = 10,
    score = 3,
    message = 'six'
WHERE
    individual_number = 6
    AND updated_at = '2023-10-05 12:15:00';

-- similarly for all the rows

Let's run it, and capture the time needed to execute it.

Now let's do the same process by using a temporary table

-- Create a temporary table named work_tbl and insert data into it
CREATE TEMPORARY TABLE work_tbl AS
SELECT *
FROM (
    SELECT 1 AS individual_number, 5 as bmi, 5 as score,"one" as message, '2023/07/31 00:00:00' AS updated_at UNION ALL
    SELECT 2 AS individual_number, 6 as bmi, 3 as score,"two" as message, '2023/07/31 00:00:00' AS updated_at UNION ALL
    SELECT 3 AS individual_number, 7 as bmi, 2 as score,"three" as message, '2023/07/31 00:00:00' AS updated_at UNION ALL
    SELECT 4 AS individual_number, 8 as bmi, 1 as score,"four" as message, '2023/07/31 00:00:00' AS updated_at UNION ALL
    SELECT 5 AS individual_number, 9 as bmi, 4 as score,"five" as message, '2023/07/31 00:00:00' AS updated_at UNION ALL
    SELECT 6A S individual_number, 10 as bmi, 3 as score,"six" as message, '2023/07/31 00:00:00' AS updated_at UNION ALL
) AS t;
-- similarly for all the rows

-- Update the results table based on data in work_tbl
UPDATE results
INNER JOIN work_tbl
ON results.individual_number = work_tbl.individual_number
AND results.updated_at = work_tbl.updated_at
SET
    results.bmi = work_tbl.update_bmi,
    results.score = work_tbl.update_score,
    results.message = work_tbl.update_message;

Let's run it, and capture the time needed to execute it.

Query with temporary table with much faster as compared to direct update in larger number of data.

In conclusion, temporary tables are not just for small or medium-sized tasks. They are a valuable tool in your SQL toolkit for handling larger data updates efficiently and safely. By incorporating temporary tables into your data update strategies, you can streamline operations, improve performance, and maintain the integrity of your database, even when working with massive datasets and improve database performance.

Happy coding.