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:

CopyINSERT INTO table (column1, column2, ...) VALUES (value1, value2, ...); 

Here is an example of an INSERT statement that inserts a new row into the employees table:

CopyINSERT 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:

CopyINSERT 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 the VALUES clause with multiple lists of values. For example:

CopyINSERT 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:

CopyINSERT 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:

CopyINSERT 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:

CopyINSERT 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 the INSERT 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:

CopyWITH 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.