Moving Data Between Tables in SQL

1. Introduction

Effective data management in SQL encompasses not only storing and retrieving information but also skillfully manipulating and transferring data between tables. This capability is vital for maintaining organized databases that underpin various business functions.

In this tutorial, let’s see the essential SQL commands and strategies for seamlessly moving data between tables.

Also, we’ll explore practical example use cases for this scenario using the Baeldung University database schema and sample data.

2. Sample Data

Let’s consider the Department table, which displays university department details such as id, name, and code. For example, the output lists departments like Computer Science, Electronics, and Communications, totaling five rows. Additionally, we employ the SELECT query to fetch and display all rows from the Department table:

> SELECT * FROM Department;
 id |              name              | code
----+--------------------------------+------
  1 | Computer Science               | CS
  2 | Electronics and Communications | EC
  3 | Mechanical Engineering         | ME
  4 | Civil Engineering              | CE
  5 | Mathematics                    | MA
(5 rows)

Similarly, let’s examine the Faculty table, which features faculty members in departments such as Computer Science and Mathematics, outlining their roles and employment statuses. For example, it includes professors, assistant professors, and teaching assistants from various departments, totaling seven rows:

> SELECT * FROM Faculty;
 id  |      name      | national_id |      position       | start_date |  end_date  | department_id | active
-----+----------------+-------------+---------------------+------------+------------+---------------+--------
   1 | Anubha Gupta   |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2 | t
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |            |             1 | t
 121 | Risa Sodi      |  1409239017 | Associate Professor | 2010-01-11 |            |             1 | t
 512 | Casper Jones   |  4253513301 | Teaching Assistant  | 2021-04-11 |            |             3 | t
 601 | Sussie Smith   |  1657230918 | Professor           | 2019-01-11 | 2027-02-18 |             5 | t
 740 | Kira Wass      |  2314623876 | Teaching Assistant  | 2021-09-11 |            |             4 | t
 741 | Sophia Ker     |  2314437876 | Teaching Assistant  | 2022-08-11 |            |             4 | t
(7 rows)

3. Building Tables From Other Tables

Now, let’s create a new database table utilizing data from existing tables. We’ll quickly define the table named Department_faculties, featuring columns for faculty id, name, position, department name, and department code. The PRIMARY KEY constraint ensures each ID is unique, and the UNIQUE constraint ensures uniqueness across the table:

> CREATE TABLE Department_faculties
(
    id INT PRIMARY KEY NOT Null,
    name VARCHAR (50),
    position VARCHAR (50),
    department_name VARCHAR (50),
    department_code VARCHAR (4),
    UNIQUE (id)
);
CREATE TABLE

Subsequently, this INSERT statement populates the table Department_faculties by selecting id, department name, and position from the Faculty table, and name and code from the Department table. It employs an INNER JOIN to connect records based on matching department IDs (Faculty.department_id = Department.id), thereby merging faculty details with respective department names and codes seamlessly:

> INSERT INTO Department_faculties(id, name, position, department_name, department_code) SELECT fac.id, fac.name, fac.position, dep.name, dep.code FROM Department dep INNER JOIN Faculty fac ON fac.department_id = dep.id;
INSERT 0 7
>

Further, the output message “INSERT 0 7” confirms the successful insertion of seven rows into the Department_faculties table, consolidating information from both source tables seamlessly:

> SELECT * FROM Department_faculties;
 id  |      name      |      position       |        department_name         | department_code
-----+----------------+---------------------+--------------------------------+-----------------
   1 | Anubha Gupta   | Professor           | Electronics and Communications | EC
 111 | AV Subramanium | Assistant Professor | Computer Science               | CS
 121 | Risa Sodi      | Associate Professor | Computer Science               | CS
 512 | Casper Jones   | Teaching Assistant  | Mechanical Engineering         | ME
 601 | Sussie Smith   | Professor           | Mathematics                    | MA
 740 | Kira Wass      | Teaching Assistant  | Civil Engineering              | CE
 741 | Sophia Ker     | Teaching Assistant  | Civil Engineering              | CE
(7 rows)

Here, the SELECT query retrieves all rows from the table Department_faculties, displaying columns for faculty id, name, position, department name, and department code. The output lists faculty members across various departments, showing their respective roles and affiliations.

4. Conclusion

In this tutorial, mastering SQL data movement techniques is crucial for ensuring efficient database operations. Commands like INSERT INTO facilitate seamless data integration and transformation, optimizing database performance by minimizing redundant operations and maximizing resource utilization.

These techniques empower database administrators and developers to effectively manage complex data scenarios, from archival to real-time updates, while mitigating the risks of data inconsistencies or errors.