SQL SELECT INTO Clause Tutorial

Example Table

We will use the following table named employees for our examples:

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

    INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
    (1, 'John', 'Doe', 'HR', 50000.00),
    (2, 'Jane', 'Smith', 'Finance', 60000.00),
    (3, 'Emily', 'Jones', 'IT', 70000.00),
    (4, 'Michael', 'Brown', 'IT', 80000.00),
    (5, 'Sarah', 'Davis', 'Finance', 55000.00);
    

Employees Table

employee_id	first_name	last_name	department	salary
1	John	Doe	HR	50000.00
2	Jane	Smith	Finance	60000.00
3	Emily	Jones	IT	70000.00
4	Michael	Brown	IT	80000.00
5	Sarah	Davis	Finance	55000.00

Using the SQL SELECT INTO Clause

The SQL SELECT INTO clause is used to create a new table and insert data into it from an existing table. This is useful for creating backups or copying data for analysis.

Example: Creating a Backup Table

To create a backup of the employees table, use the following query:

    SELECT * INTO employees_backup
    FROM employees;
    

Result:

Query	SELECT * INTO employees_backup FROM employees;
Result	A new table named employees_backup is created with the same data as the employees table.

Example: Creating a Table with Specific Columns

To create a new table with only specific columns from the employees table, use the following query:

    SELECT first_name, last_name INTO employees_names
    FROM employees;
    

Result:

Query	SELECT first_name, last_name INTO employees_names FROM employees;
Result	A new table named employees_names is created with only the first_name and last_name columns from the employees table.

Example: Creating a Table with Filtered Data

To create a new table with only employees from the IT department, use the following query:

    SELECT * INTO it_employees
    FROM employees
    WHERE department = 'IT';
    

Result:

Query	SELECT * INTO it_employees FROM employees WHERE department = 'IT';
Result	A new table named it_employees is created with only the employees from the IT department.