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.