INSERT COMMAND

 The INSERT command in SQL is used to add new rows of data to a table. It is one of the most frequently used Data Manipulation Language (DML) commands, enabling users to populate tables with initial or additional data.

---

1. Purpose

The INSERT command is used to:

• Add a single row to a table.
• Add multiple rows in one operation.
• Insert data from another table using a query.

---

2. Syntax

A. Basic Insert Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• table_name: Name of the table where data will be inserted.
• column1, column2, ...: List of columns where data will be inserted.
• value1, value2, ...: Corresponding values for the specified columns.

---

B. Inserting All Columns

If all columns are to be populated, you can omit the column list:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

---

C. Insert Multiple Rows

INSERT ALL
  INTO table_name (column1, column2) VALUES (value1, value2)
  INTO table_name (column1, column2) VALUES (value3, value4)
SELECT * FROM dual;

---

D. Insert Data from Another Table

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

---

E. Insert Default Values

If the table has default values defined for some columns:

INSERT INTO table_name (column1, column2)
VALUES (value1, DEFAULT);

---

F. Insert Using Substitution Variables

You can use substitution variables to insert data dynamically (e.g., in Oracle SQL*Plus):

INSERT INTO employees (emp_id, emp_name, department_id)
VALUES (&emp_id, '&emp_name', &department_id);

---

3. Examples

A. Insert a Single Row

INSERT INTO employees (emp_id, emp_name, department_id, salary)
VALUES (101, 'Alice', 20, 50000);

---

B. Insert All Columns

INSERT INTO employees
VALUES (102, 'Bob', 30, 60000);

---

C. Insert Multiple Rows

INSERT ALL
  INTO employees (emp_id, emp_name, department_id, salary) VALUES (103, 'Charlie', 40, 55000)
  INTO employees (emp_id, emp_name, department_id, salary) VALUES (104, 'David', 50, 62000)
SELECT * FROM dual;

---

D. Insert Data from Another Table

Suppose we have another table new_employees with the same structure as employees:

INSERT INTO employees (emp_id, emp_name, department_id, salary)
SELECT emp_id, emp_name, department_id, salary
FROM new_employees
WHERE department_id = 30;

---

E. Insert Default Values

If the department_id column has a default value defined:

INSERT INTO employees (emp_id, emp_name, salary)
VALUES (105, 'Eve', 70000);

---

4. Key Points

A. Default and Nullable Columns

• Columns not specified in the INSERT statement must:
  • Have a default value.
  • Be nullable (i.e., allow NULL values).
• If neither is true, the INSERT operation will fail.

---

B. Data Type Compatibility

• Values being inserted must match the column's data type. For example:
  • A VARCHAR2 column cannot accept NUMBER values.
  • Use functions like TO_DATE or TO_CHAR for conversion if needed.

---

C. Transactions

• By default, an INSERT command is part of a transaction:
  • Changes are not permanent until you issue a COMMIT.
  • You can roll back the operation with ROLLBACK.

---

D. Inserting Special Characters

To insert values with single quotes or special characters, use escape sequences:

INSERT INTO employees (emp_id, emp_name)
VALUES (106, 'O''Connor');

---

5. Performance Considerations

A. Bulk Inserts

For inserting a large number of rows, consider using:

• Oracle SQL Loader for external files.
• INSERT ALL or INSERT INTO ... SELECT for internal data.

---

B. Avoiding Redundant Data

To prevent duplicate rows:

• Use constraints like UNIQUE or PRIMARY KEY.
• Use conditions in INSERT INTO ... SELECT:

  INSERT INTO employees (emp_id, emp_name)
  SELECT emp_id, emp_name
  FROM new_employees
  WHERE emp_id NOT IN (SELECT emp_id FROM employees);
  

---

6. Verifying Data

After inserting data, verify it using a SELECT query:

SELECT * FROM employees
WHERE emp_id = 101;

---

7. Common Errors

A. ORA-00947: Not enough values

Occurs when the number of values in the INSERT statement is less than the number of columns specified.

B. ORA-01722: Invalid number

Occurs when inserting non-numeric data into a numeric column.

C. ORA-00001: Unique constraint violated

Occurs when inserting a value that violates a UNIQUE or PRIMARY KEY constraint.

---

8. Summary

Feature	Details
Single Row Insert	INSERT INTO table_name VALUES (...);
Multiple Row Insert	INSERT ALL INTO ... SELECT * FROM dual;
Insert from Query	INSERT INTO target_table SELECT ...;
Default Values	Use DEFAULT for columns with default settings.
Transaction Control	Use COMMIT to save or ROLLBACK to undo.

---