The INSERT Query in SQL

codermvn

The INSERT query in SQL is used to add new rows of data to a table in a database. This is a fundamental operation for populating tables with data.

Basic Syntax

The basic syntax for an INSERT statement is:

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

Example

Let's assume we have a table named employees with columns employee_id, first_name, last_name, and salary.

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

To insert a new row into the employees table, you would use the following INSERT statement:

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000.00);

Inserting Multiple Rows

You can insert multiple rows in a single INSERT statement by separating each set of values with a comma.

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES
(2, 'Jane', 'Smith', 60000.00),
(3, 'Jim', 'Brown', 55000.00),
(4, 'Jake', 'White', 45000.00);

Inserting Data into All Columns

If you are inserting values into all columns of a table, you can omit the column names from the INSERT statement. However, the values must be in the same order as the columns in the table.

INSERT INTO employees
VALUES (5, 'Alice', 'Johnson', 70000.00);

Inserting Data from Another Table

You can insert data into a table from another table using a SELECT statement.

INSERT INTO employees (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM new_employees;

Inserting Data with Default Values

If a table has columns with default values and you want to insert data using those defaults, you can use the DEFAULT keyword.

Assuming the employees table has a hire_date column with a default value:

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE DEFAULT GETDATE()
);
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (6, 'Bob', 'Marley', 80000.00);

In this case, the hire_date will automatically be set to the current date.

The INSERT statement is a fundamental SQL operation for adding new data to tables. Understanding how to use it efficiently and correctly is essential for database management and manipulation. Whether you are inserting single rows, multiple rows, or data from another table, the INSERT query provides the flexibility and functionality needed to populate your tables with data.