3.1 Inserting Data: INSERT INTO

The INSERT INTO statement in SQL allows you to add new rows of data into a table. This operation is fundamental for populating and updating a database with fresh information.

3.1.1 Overview of INSERT INTO

• Purpose: Add one or more rows to a table.
• Syntax:

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

  • table_name: The name of the table where the data will be inserted.
  • Columns: The columns to which data is being inserted.
  • Values: The values corresponding to the specified columns.

3.1.2 Key Points

1. Specifying Columns:
   • You can specify only the columns you want to insert data into. The other columns will either have their default values or remain NULL.
   • Example: Insert only into name and age columns:

     INSERT INTO employees (name, age)
     VALUES ('John Doe', 30);
     

2. All Columns:
   • If you're inserting data into all columns, you can omit the column list. However, the number and order of values must match the table definition.
   • Example:

     INSERT INTO employees
     VALUES (1, 'John Doe', 30, 'IT');
     

3. Multiple Rows Insertion:
   • You can insert multiple rows in one query by separating value sets with commas.
   • Example:

     INSERT INTO employees (name, age, department)
     VALUES 
       ('Alice', 25, 'HR'),
       ('Bob', 28, 'Finance'),
       ('Charlie', 35, 'IT');
     

3.1.3 Using Default Values

If a column has a default value (defined in the table schema), you can omit it during insertion, and SQL will automatically use the default value.

• Example: Insert a new employee without specifying the department:

  INSERT INTO employees (name, age)
  VALUES ('David', 40);
  

3.1.4 Error Handling and Constraints

SQL enforces table constraints like primary keys, foreign keys, and unique constraints during insertion:

• Primary Key Violation:
  Attempting to insert a row with a duplicate primary key will result in an error.
  Example:

  INSERT INTO employees (id, name)
  VALUES (1, 'John'); -- Error if ID 1 already exists
  

• Not Null Constraint:
  If a column requires a value (NOT NULL), inserting a NULL will fail.
  Example:

  INSERT INTO employees (name, age)
  VALUES (NULL, 25); -- Error if 'name' column cannot be NULL
  

• Foreign Key Violation:
  Data being inserted into a column linked by a foreign key must match existing data in the referenced table.

3.1.5 Advanced Usage

1. Insert Data from Another Table:
   You can insert data into a table by selecting it from another table.
   • Example:

     INSERT INTO archived_employees (id, name, department)
     SELECT id, name, department
     FROM employees
     WHERE status = 'inactive';
     

2. Insert with Calculated Values:
   Use SQL functions to generate or manipulate values during insertion.
   • Example: Insert the current timestamp:

     INSERT INTO logs (user_id, action, timestamp)
     VALUES (123, 'Login', CURRENT_TIMESTAMP);
     

Real-World Examples

1. Adding a New Customer:

   INSERT INTO customers (name, email, city)
   VALUES ('Jane Smith', 'jane@example.com', 'Los Angeles');
   

2. Recording a Sale:

   INSERT INTO sales (product_id, customer_id, quantity, sale_date)
   VALUES (101, 5, 3, '2024-12-14');
   

3. Batch Insertion:

   INSERT INTO inventory (product_name, quantity, price)
   VALUES 
     ('Laptop', 10, 800),
     ('Monitor', 15, 150),
     ('Keyboard', 20, 20);
   

Best Practices for INSERT INTO

1. Validate Input Data: Ensure the values being inserted match the column data types and constraints.
2. Use Transactions for Bulk Operations: When inserting many rows, use transactions to ensure atomicity.
   • Example:

     BEGIN TRANSACTION;
     INSERT INTO employees (name, department) VALUES ('Alice', 'HR');
     INSERT INTO employees (name, department) VALUES ('Bob', 'Finance');
     COMMIT;
     

3. Avoid Hardcoding: Use parameters in prepared statements for dynamic values to prevent SQL injection.

Conclusion

The INSERT INTO statement is the gateway to adding new data into a database. Understanding its syntax, constraints, and advanced usage will empower you to populate your tables efficiently and accurately. This foundational skill sets the stage for managing data effectively in any SQL-based system.