SQL INSERT INTO

The INSERT INTO statement in SQL is used to add new records to a table. There are two main ways to use the INSERT INTO statement:

1. Inserting data into all columns
2. Inserting data into specific columns

1. Inserting Data into All Columns

When you want to insert data into all columns of a table, you don't need to specify the column names. However, the values must be in the same order as the columns in the table.

Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example: Suppose we have a table called employees with the following structure:

CREATE TABLE employees (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

To insert a new employee record:

INSERT INTO employees
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

2. Inserting Data into Specific Columns

When you want to insert data into specific columns, you need to specify the column names.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example: Using the same employees table, to insert a new employee record specifying only certain columns:

INSERT INTO employees (id, first_name, email)
VALUES (2, 'Jane', 'jane.doe@example.com');

Inserting Multiple Rows

You can insert multiple rows into a table in a single INSERT INTO statement by separating each row's values with commas.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
    (value1a, value2a, value3a, ...),
    (value1b, value2b, value3b, ...),
    ...;

Example: To insert multiple employee records:

INSERT INTO employees (id, first_name, last_name, email)
VALUES 
    (3, 'Alice', 'Smith', 'alice.smith@example.com'),
    (4, 'Bob', 'Brown', 'bob.brown@example.com');

Using INSERT INTO with a SELECT Statement

You can also insert data into a table by selecting data from another table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM another_table
WHERE condition;

Example: Assume there is another table called new_employees with the same structure as the employees table. To copy data from new_employees to employees:

INSERT INTO employees (id, first_name, last_name, email)
SELECT id, first_name, last_name, email
FROM new_employees
WHERE id > 5;

These are the basic ways to use the INSERT INTO statement in SQL to add new records to a table.