Data Transfer Between Tables in SQL

Introduction

In relational databases, efficiently transferring data between tables is a common requirement. This tutorial will explore the standard SQL methods for copying data from one table to another, covering scenarios where tables have identical schemas and differing schemas. We will focus on the INSERT INTO statement combined with SELECT to achieve this, ensuring clarity and best practices for data manipulation.

Basic Data Copying with INSERT INTO and SELECT

The most straightforward way to copy data from one table (the source) to another (the destination) is using the INSERT INTO statement in conjunction with a SELECT statement.

The fundamental syntax is:

INSERT INTO destination_table
SELECT *
FROM source_table;

This statement inserts all rows and columns from source_table into destination_table. It assumes that destination_table already exists and has a compatible schema with source_table. “Compatible” means either the same schema, or that the data types of the selected columns in the source_table match those of the corresponding columns in the destination_table.

Example:

Let’s say we have two tables, employees_old and employees_new, with the same structure:

CREATE TABLE employees_old (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE employees_new (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

To copy all data from employees_old to employees_new, you would use the following query:

INSERT INTO employees_new
SELECT *
FROM employees_old;

Handling Differing Schemas

If the schemas of the source and destination tables are different (different column names or a different number of columns), you must explicitly specify the columns to be inserted and selected.

The syntax is:

INSERT INTO destination_table (column1, column2, column3)
SELECT column_a, column_b, column_c
FROM source_table;

Here, column1, column2, and column3 are the columns in the destination_table, and column_a, column_b, and column_c are the corresponding columns in the source_table. The order of columns in both lists must match, and data types must be compatible.

Example:

Suppose employees_old has a salary column that employees_new does not. We want to copy all data except the salary column.

CREATE TABLE employees_old (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

CREATE TABLE employees_new (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

The query would be:

INSERT INTO employees_new (employee_id, first_name, last_name, department)
SELECT employee_id, first_name, last_name, department
FROM employees_old;

Creating a Table and Copying Data Simultaneously

SQL Server allows you to create a new table and copy data into it in a single step using the SELECT INTO statement.

The syntax is:

SELECT *
INTO new_table_name
FROM existing_table_name;

This statement creates a new table named new_table_name with the same schema and data as existing_table_name. This is a convenient shortcut, but it’s important to note:

• The new table will be created in the current database and schema.
• Indexes, constraints, and other table properties are not copied. You’ll need to create those manually after the table is created.
• If new_table_name already exists, the statement will fail.

Example:

To create a copy of employees_old named employees_backup, you would use:

SELECT *
INTO employees_backup
FROM employees_old;

Important Considerations

• Data Types: Ensure data types are compatible between the source and destination columns to avoid errors or data truncation.
• Identity Columns: If the destination table has an identity column, you may need to enable identity insertion to allow the insertion of values into that column.
• Performance: For large tables, consider batching the insertion process or using other performance optimization techniques.
• Transactions: Enclose the data transfer process within a transaction to ensure data consistency and allow for rollback in case of errors.
• Constraints: Ensure that the inserted data does not violate any constraints defined on the destination table (e.g., primary key constraints, foreign key constraints, unique constraints).