INSERT - Adding Data to Tables in SQL

The INSERT statement in SQL is used to add new records (rows) to a table. It is one of the most commonly used SQL commands and allows you to add data to your database. There are different ways to use the INSERT statement, depending on how you want to add data to the table.

1. Basic INSERT INTO Statement

The basic INSERT INTO statement adds a single row of data to a table. You need to specify the table name and the values for each column in the table.

Example: Basic INSERT INTO

      -- Insert a new student record into the 'Students' table
      INSERT INTO Students (StudentID, Name, Age)
      VALUES (1, 'John Doe', 20);
    

In this example, a new record is inserted into the 'Students' table. The values (1, 'John Doe', 20) correspond to the columns 'StudentID', 'Name', and 'Age'.

2. Inserting Data without Specifying Column Names

If you provide values for every column in the table, you can omit the column names in the INSERT INTO statement. The values will be inserted in the order in which the columns were defined when the table was created.

Example: Inserting Data without Specifying Column Names

      -- Insert a new student record into the 'Students' table (without column names)
      INSERT INTO Students
      VALUES (2, 'Jane Smith', 22);
    

In this example, the new record for 'Jane Smith' is inserted into the 'Students' table without explicitly specifying column names. The values are inserted in the order of the columns in the table definition.

3. Inserting Multiple Rows of Data

You can insert multiple rows of data at once by specifying multiple sets of values in a single INSERT INTO statement. This can be more efficient than inserting each row individually.

Example: Inserting Multiple Rows

      -- Insert multiple student records into the 'Students' table
      INSERT INTO Students (StudentID, Name, Age)
      VALUES
        (3, 'Mark Lee', 21),
        (4, 'Sarah Brown', 23),
        (5, 'Michael Green', 19);
    

In this example, three new student records are inserted into the 'Students' table in a single INSERT INTO statement. Each set of values represents a different record.

4. Inserting Data from Another Table

You can insert data into a table from another table using the INSERT INTO statement combined with a SELECT statement. This is useful when you need to copy data between tables or migrate data based on certain conditions.

Example: Inserting Data from Another Table

      -- Insert student records from the 'NewStudents' table into the 'Students' table
      INSERT INTO Students (StudentID, Name, Age)
      SELECT StudentID, Name, Age
      FROM NewStudents;
    

In this example, data from the 'NewStudents' table is inserted into the 'Students' table. The SELECT statement retrieves the 'StudentID', 'Name', and 'Age' columns from the 'NewStudents' table, and the INSERT INTO statement adds them to the 'Students' table.

5. Inserting Data with Default Values

If a column has a DEFAULT constraint, you can insert data into the table without providing a value for that column. The database will automatically insert the default value.

Example: Inserting Data with Default Values

      -- Insert a student record with a default value for the 'Status' column
      INSERT INTO Students (StudentID, Name, Age)
      VALUES (6, 'Emily White', 20);
    

In this example, the 'Status' column may have a default value (e.g., 'Active'). Since the 'Status' column is not included in the INSERT INTO statement, the database will automatically use the default value for this column.

6. Handling NULL Values

If a column allows NULL values, you can insert a NULL value into that column when adding a new record. This is useful when you don't have data to provide for that column.

Example: Inserting NULL Values

      -- Insert a student record with a NULL value for the 'PhoneNumber' column
      INSERT INTO Students (StudentID, Name, Age, PhoneNumber)
      VALUES (7, 'David Black', 24, NULL);
    

In this example, the 'PhoneNumber' column is explicitly set to NULL for 'David Black'. This is valid if the 'PhoneNumber' column allows NULL values.

7. Using Auto-Increment for Primary Key

If a column is defined with the AUTO_INCREMENT property (or similar, depending on the database system), you do not need to provide a value for that column when inserting new records. The database will automatically generate a unique value for this column.

Example: Using Auto-Increment for Primary Key

      -- Insert a student record where the 'StudentID' is auto-generated
      INSERT INTO Students (Name, Age)
      VALUES ('Alice Brown', 21);
    

In this example, the 'StudentID' is auto-generated, so we don't need to provide a value for it. The database will automatically assign a unique 'StudentID' to the new student.

Conclusion

The INSERT statement in SQL is a powerful tool for adding data to a database. Whether you are inserting a single record, multiple records, or transferring data from another table, understanding how to use INSERT INTO correctly is essential for managing data in a relational database. By using techniques such as handling default values, NULL values, and auto-incrementing primary keys, you can efficiently manage the insertion of data into your tables.