MySQL:- Temporary Tables

Background

Was reading through some documentation and it touched on temporary tables in MySQL.

Sample

Here is a sample code.

Outline

  1. Create database\schema if it does not exist
  2. Begin new transaction using start transaction
  3. Create temporary table ( accountTransaction )
  4. create temporary table ( accountBalance )
  5. Add data into temporary table ( accountTransaction )
  6. Summary data in detail table ( accountTransaction ) into summary table ( accountBalance )
  7. Display summarized data
  8. Clean Up
    • Drop temporary tables
      • Drop temporary table – accountTransaction
      • Drop temporary table – accountBalance

SQL


create database if not exists dblab
;

use dblab;

start transaction;

    /*
     * Create Temporary Table - accountTransaction
    */
    create temporary table if not exists accountTransaction
    (
    
        id              int                 not null
        auto_increment 
        primary key
        
        , accountNumber varchar(30)         not null
        
        , ts            datetime            not null
          DEFAULT       CURRENT_TIMESTAMP
        
        , amount        DECIMAL(20,2)       not null
        
    )
    ENGINE INNODB
    ;

    /*
     * Create Temporary Table - accountBalance
    */
    create temporary table if not exists accountBalance
    (
    
        id              int                 not null
        auto_increment 
        primary key
        
        , accountNumber varchar(30)         not null
        
        , amount        DECIMAL(20,2)       not null
        
        , tsInitial     datetime            not null
        
        , tsLast        datetime            not null
            
    )
    ENGINE INNODB
    ;

    /*
     * Add data into accountTransaction
    */
    insert into accountTransaction
    (
          accountNumber, amount
    )
    values
      ( 'A1', 100 )
    , ( 'A1', 105 )
    , ( 'A1', 215 )
    ;

    /*
     * Add data into accountTransaction
    */
    insert into accountTransaction
    (
          accountNumber, amount
    )
    values
      ( 'B2', 300 )
    , ( 'B2', -425 )
    ;

    /*
     * Summarize data
    */
    insert into accountBalance
    (
            accountNumber
          , amount
          , tsInitial
          , tsLast
    )
    select
            accountNumber
          , sum(amount)
          , min(ts) as tsMin      
          , max(ts) as tsMax
    
    from accountTransaction
    
    group by
        accountNumber
    
    ;
   
   
    /*
     * Display data
    */
    select
    
          accountNumber
        
        , amount       
        
        , tsInitial    
        
        , tsLast       
    
    
    from   accountBalance
    
    order by
        accountNumber
    
    ;

    /*
     * Drop temporary tables
    */
    drop temporary table if exists accountTransaction;


    drop temporary table if exists accountBalance;

rollback;

Source Code

Github

Gist

DanielAdeniji/mysql.temporaryTable.bank.transaction.sql

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s