Insert into in SQL
The INSERT
statement is used to insert new rows into a table in a database.
Here is the basic syntax of an INSERT
statement:
INSERT INTO table (column1, column2, ...) VALUES (value1, value2, ...);
Here is an example of an INSERT
statement that inserts a new row into the employees
table:
INSERT INTO employees (first_name, last_name, salary, department) VALUES ('John', 'Doe', 50000, 'Sales');
This INSERT
statement will insert a new row into the employees
table with the specified values for the first_name
, last_name
, salary
, and department
columns.
You can also use the INSERT INTO ... SELECT
syntax to insert rows based on the result set of a SELECT
statement. For example:
INSERT INTO employees (first_name, last_name, salary, department) SELECT first_name, last_name, salary, department FROM old_employees WHERE department = 'Sales';
This INSERT
statement will insert all rows from the old_employees
table where the department
column is "Sales" into the employees
table.
points to consider when working with the Insert
Here are a few additional points to consider when using the INSERT
statement in SQL:
- You can insert multiple rows into a table in a single
INSERT
statement by using theVALUES
clause with multiple lists of values. For example:
INSERT INTO employees (first_name, last_name, salary, department) VALUES ('John', 'Doe', 50000, 'Sales'), ('Jane', 'Doe', 60000, 'Marketing');
This INSERT
statement will insert two rows into the employees
table with the specified values.
- You can use the
DEFAULT
keyword to insert the default value for a column. For example:
INSERT INTO employees (first_name, last_name, salary, department, hire_date) VALUES ('John', 'Doe', 50000, 'Sales', DEFAULT);
This INSERT
statement will insert a new row into the employees
table with the specified values for the first_name
, last_name
, salary
, and department
columns, and the default value for the hire_date
column.
- You can use the
RETURNING
clause to return the values of the inserted row. For example:
INSERT INTO employees (first_name, last_name, salary, department) VALUES ('John', 'Doe', 50000, 'Sales') RETURNING employee_id, first_name, last_name, salary, department;
This INSERT
statement will insert a new row into the employees
table with the specified values, and return
- You can use the
ON CONFLICT
clause to handle situations where a unique constraint is violated when inserting a new row. For example:
INSERT INTO employees (employee_id, first_name, last_name, salary, department) VALUES (1, 'John', 'Doe', 50000, 'Sales') ON CONFLICT (employee_id) DO UPDATE SET salary = excluded.salary;
This INSERT
statement will insert a new row into the employees
table with the specified values. If the employee_id
column already exists in the table and violates a unique constraint, the ON CONFLICT
clause will update the existing row with the value from the excluded
table (which represents the new row being inserted).
- You can use the
WITH
clause to specify a common table expression (CTE) that can be used in theINSERT
statement. A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. For example:
WITH new_employees AS ( SELECT 'John' AS first_name, 'Doe' AS last_name, 50000 AS salary, 'Sales' AS department UNION ALL SELECT 'Jane' AS first_name, 'Doe' AS last_name, 60000 AS salary, 'Marketing' AS department ) INSERT INTO employees (first_name, last_name, salary, department) SELECT * FROM new_employees;
This INSERT
statement will insert multiple rows into the employees
table using the new_employees
CTE.