5 Ways Insert Statement

Insert statements are a fundamental component of database management, allowing users to add new records to a database table. There are several ways to use insert statements, each with its own unique characteristics and applications. In this article, we will explore five different ways to use insert statements, including their syntax, examples, and use cases.

Inserting a Single Row into a Table

To insert a single row into a table, you can use the basic insert statement syntax, which includes the insert keyword, the into keyword, the table name, and the values keyword. The values keyword is followed by a list of values that correspond to the columns in the table.

For example, suppose we have a table called “employees” with columns for employee ID, name, and department. To insert a new employee into the table, we can use the following statement:

INSERT INTO employees (employee_id, name, department)
VALUES (1, ‘John Doe’, ‘Sales’);

This statement inserts a new row into the employees table with the specified values for employee ID, name, and department.

Inserting Multiple Rows into a Table

To insert multiple rows into a table, you can use a single insert statement with multiple values lists. Each values list is separated by a comma, and the entire statement is terminated with a semicolon.

For example, suppose we want to insert multiple employees into the employees table. We can use the following statement:

INSERT INTO employees (employee_id, name, department)
VALUES
  (1, ‘John Doe’, ‘Sales’),
  (2, ‘Jane Smith’, ‘Marketing’),
  (3, ‘Bob Johnson’, ‘IT’);

This statement inserts three new rows into the employees table with the specified values for employee ID, name, and department.

Inserting Data from a Select Statement

To insert data from a select statement, you can use the insert into select syntax. This syntax allows you to insert data from one table into another table based on a select statement.

For example, suppose we have two tables, “employees” and “new_employees”, and we want to insert all employees from the “new_employees” table into the “employees” table. We can use the following statement:

INSERT INTO employees (employee_id, name, department)
SELECT employee_id, name, department
FROM new_employees;

This statement inserts all rows from the new_employees table into the employees table.

Inserting Data with Default Values

To insert data with default values, you can use the default keyword in the insert statement. The default keyword specifies that the default value for a column should be used instead of a specified value.

For example, suppose we have a table called “orders” with columns for order ID, customer ID, and order date. The order date column has a default value of the current date. To insert a new order into the table with the default order date, we can use the following statement:

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (1, 1, DEFAULT);

This statement inserts a new row into the orders table with the specified values for order ID and customer ID, and the default value for order date.

Inserting Data with Auto-Incrementing Primary Keys

To insert data with auto-incrementing primary keys, you can use the auto_increment keyword in the create table statement. The auto_increment keyword specifies that the primary key column should automatically increment for each new row inserted into the table.

For example, suppose we have a table called “products” with columns for product ID, name, and price. The product ID column is an auto-incrementing primary key. To insert a new product into the table, we can use the following statement:

INSERT INTO products (name, price)
VALUES (‘Product A’, 19.99);

This statement inserts a new row into the products table with the specified values for name and price, and an automatically generated product ID.

Meta Description: Learn about the different ways to use insert statements in database management, including inserting a single row, multiple rows, data from a select statement, data with default values, and data with auto-incrementing primary keys.