Insert Data In a Table | Tips and Tricks | Oracle SQL PLSQL
The `INSERT` statement in Oracle SQL is used to add one or more rows of data into a table. Here's an overview of its elements:
```sql
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
```
Explanation:
1. *INSERT INTO:*
Specifies the name of the table where you want to insert data.
2. *table_name:*
The name of the table into which you want to insert data.
3. *Columns (Optional):*
You can specify the columns for which you are providing values. If you omit the column list, the values must be listed in the same order as the columns in the table.
Syntax: `(column1, column2, column3, ...)`
4. *VALUES:*
Indicates the beginning of the values to be inserted.
5. *Values:*
Specifies the actual values to be inserted into the corresponding columns.
Syntax: `(value1, value2, value3, ...)`
Example without column list:
```sql
INSERT INTO employees VALUES (101, 'John Doe', 'Manager', 50000);
```
Example with column list:
```sql
INSERT INTO employees (employee_id, employee_name, job_title, salary)
VALUES (101, 'John Doe', 'Manager', 50000);
```
Additional elements:
*Subquery:*
Instead of specifying values directly, you can use a subquery to retrieve data from another table and insert it into the target table.
```sql
INSERT INTO employees (employee_id, employee_name, job_title, salary)
SELECT emp_id, emp_name, emp_job, emp_salary
FROM temporary_employees;
```
*DEFAULT Values:*
You can use the `DEFAULT` keyword to insert default values defined for columns in the table.
```sql
INSERT INTO employees (employee_id, employee_name, job_title, hire_date)
VALUES (102, 'Jane Smith', 'Analyst', DEFAULT);
```
*RETURNING Clause (Oracle-specific):*
Allows you to return values generated by the `INSERT` statement, such as auto-generated keys.
```sql
INSERT INTO employees (employee_id, employee_name, job_title, salary)
VALUES (103, 'Alice Johnson', 'Developer', 60000)
RETURNING employee_id INTO v_employee_id;
```
These elements provide flexibility in inserting data into tables based on specific requirements and scenarios.