SQL INSERT INTO Statement

 The SQL INSERT INTO statement is used to add new records (or rows) to a database table. The general syntax can be broken down into two main forms:

1. Inserting Values into All Columns

This method inserts a new row into all columns of a table. You must provide values for each column in the correct order.

Syntax:

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

• table_name: The name of the table where you want to insert the data.
• value1, value2, value3, ...: The values to insert into the respective columns.

Example:

INSERT INTO employees 
VALUES (1, 'John Doe', 'Software Engineer', 50000);

This inserts a new row into the employees table with an ID of 1, name 'John Doe', job title 'Software Engineer', and salary 50000.

2. Inserting Values into Specific Columns

This method allows you to specify values for only some columns, while other columns will use default values or NULL if they are nullable.

Syntax:

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

• column1, column2, column3, ...: The columns for which you want to insert values.
• value1, value2, value3, ...: The corresponding values for the columns.

Example:

INSERT INTO employees (name, job_title, salary)
VALUES ('Jane Smith', 'Data Analyst', 55000);

In this example, we insert a new employee with the name 'Jane Smith', job title 'Data Analyst', and salary 55000. The id column is omitted, so it would auto-generate based on the table's settings (e.g., an auto-increment field).

3. Inserting Multiple Rows

You can also insert multiple rows in a single INSERT INTO statement. Each set of values is enclosed in parentheses and separated by commas.

Syntax:

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

Example:

INSERT INTO employees (name, job_title, salary)
VALUES
    ('Alice Brown', 'HR Manager', 60000),
    ('Bob White', 'Marketing Specialist', 45000),
    ('Charlie Black', 'Sales Executive', 40000);

This example inserts three rows into the employees table with the specified values for the name, job_title, and salary columns.

4. Inserting Data from Another Table

You can also insert data into a table based on a SELECT query from another table. This is useful when you want to copy data from one table to another.

Syntax:

INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;

Example:

INSERT INTO new_employees (name, job_title, salary)
SELECT name, job_title, salary
FROM employees
WHERE department = 'Sales';

This example copies all employees from the employees table who belong to the 'Sales' department into the new_employees table.