SQL INSERT INTO
The SQL INSERT INTO statement is used to insert new rows of data into a database table. It is one of the fundamental SQL operations and is crucial for adding data to a database. Here’s a breakdown of how the INSERT INTO statement works:
Syntax:
|
1 2 |
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); |
INSERT INTO: This is the keyword that starts the INSERT INTO statement.table_name: This is the name of the table into which you want to insert data. You should specify the name of the target table.(column1, column2, column3, ...): This is an optional part of the statement where you can specify the column names in the target table where you want to insert data. If you omit this part, you must provide values for all columns in the same order as they appear in the table.VALUES: This is another keyword that signals the start of the values that you want to insert.(value1, value2, value3, ...): Here, you provide the actual values you want to insert into the specified columns. The number of values must match the number of columns you specified, or the columns you’re inserting into if you didn’t specify column names explicitly.
Example 1: Inserting data into a table with specified column names
|
1 2 |
INSERT INTO employees (first_name, last_name, job_title, salary) VALUES ('John', 'Doe', 'Software Engineer', 75000); |
In this example, we are inserting a new row into the “employees” table. We specify the column names (first_name, last_name, job_title, and salary) and provide corresponding values for each column.
Example 2: Inserting data into a table without specifying column names
|
1 2 |
INSERT INTO customers VALUES (1001, 'Alice', 'Smith', 'alice@example.com', '123 Main St'); |
In this example, we are inserting data into the “customers” table without explicitly specifying column names. The values provided are inserted into the columns in the same order as they appear in the table.
It’s important to note that you should ensure the data types of the values you’re inserting match the data types of the corresponding columns in the table. Also, be careful when inserting data, especially from untrusted sources, to prevent SQL injection attacks by using parameterized queries or prepared statements.
Insert Multiple Rows with a Single INSERT INTO Statement:
You can insert multiple rows in a single INSERT INTO statement by specifying multiple sets of values within the VALUES clause, separated by commas. Each set of values corresponds to a new row. Here’s an example:
|
1 2 3 4 5 |
INSERT INTO employees (first_name, last_name, job_title, salary) VALUES ('John', 'Doe', 'Software Engineer', 75000), ('Jane', 'Smith', 'Data Analyst', 60000), ('Bob', 'Johnson', 'Project Manager', 85000); |
In this example, we’re inserting three rows into the “employees” table in one go.
Using INSERT INTO with SELECT Statement:
Using the INSERT INTO statement with a SELECT statement is a powerful way to insert data into a table from another table or by generating data dynamically. This method allows you to retrieve data from one or more tables or specify literal values and insert it into a target table. Here’s how it works:
Syntax:
|
1 2 3 4 |
INSERT INTO target_table (column1, column2, column3, …) SELECT expression1, expression2, expression3, … FROM source_table WHERE condition; |
INSERT INTO target_table: This part of the statement specifies the name of the target table into which you want to insert data.(column1, column2, column3, ...): You can optionally specify the columns in the target table into which you want to insert data. If you omit this part, the columns in the target table should match the columns selected in the SELECT statement.SELECT expression1, expression2, expression3, ...: In the SELECT statement, you can specify expressions, columns, or literal values to insert into the target table.FROM source_table: Here, you specify the source table from which you want to retrieve data.WHERE condition: This part is optional and allows you to filter the rows you want to insert into the target table based on a condition.
Example:
Suppose you have two tables, “customers” and “archived_customers,” and you want to move some customer data from “customers” to “archived_customers” while also adding a timestamp for archiving. You can use the INSERT INTO statement with a SELECT statement like this:
|
1 2 3 4 |
INSERT INTO archived_customers (customer_id, first_name, last_name, archived_date) SELECT customer_id, first_name, last_name, NOW() AS archived_date FROM customers WHERE registration_date < '2023-01-01'; |
In this example:
- We specify the target table as “archived_customers.”
- We explicitly mention the columns we want to insert data into: customer_id, first_name, last_name, and archived_date.
- We use the SELECT statement to retrieve customer data from the “customers” table where the registration date is before January 1, 2023.
- We also insert the current date and time using the NOW() function as the archived_date value for each row.
This statement will insert the selected rows from the “customers” table into the “archived_customers” table, along with the archived date. This is a common use case for archiving or moving data between tables in a database.
