Inserting Data with `INSERT` Statements

Part 1: The Basic INSERT Statement

The INSERT statement is used to add new rows of data to a table. The fundamental structure of the command is straightforward, but getting the details right is crucial to avoid errors.

Let's start with a foundational reading that explains the core syntax.

As the article explains, the basic syntax is:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

There are three critical parts:

1. INSERT INTO table_name: The table you want to add data to.
2. (column1, column2, ...): The list of columns you are providing data for.
3. VALUES (value1, value2, ...): The actual data for the new row.

The order and data type of the values in the VALUES clause must correspond directly to the columns in the column list. This is similar to calling a function in C, where the arguments you pass must match the function's parameters in order and type.

Inserting a Single Row

Let's see this in action. The following video gives a concise demonstration of inserting one row of data.

Best Practice: Always Specify Columns

You might see code where the column list is omitted:
INSERT INTO users VALUES (1, 'jsmith', 'jsmith@example.com');

While this works if you provide a value for every column in the exact order they appear in the table's schema, it's considered poor practice. Why?

• Brittleness: If someone later adds, removes, or reorders a column in the table (ALTER TABLE), this query will either fail or, worse, silently insert incorrect data (e.g., an email into a username column).
• Readability: Explicitly listing the columns makes the query self-documenting. Anyone reading it knows exactly which value maps to which column without having to look up the table's schema.

Your background in systems programming will give you an appreciation for this principle: building robust, maintainable systems means avoiding shortcuts that rely on implicit, fragile assumptions.

Part 2: Handling Constraints and Defaults

Your INSERT statements will interact with the constraints we defined in the last lesson.

• NOT NULL: If a column has a NOT NULL constraint, you must provide a value for it in your INSERT statement.
• UNIQUE: The database will reject your INSERT if the value you provide for a UNIQUE column already exists in another row.
• DEFAULT: If you don't specify a column in your INSERT statement, the database will use its default value if one is defined. If no default exists, it will be NULL (which would violate a NOT NULL constraint).
• Auto-incrementing Keys: For primary key columns that auto-generate their own values (e.g., using IDENTITY in SQL Server or SERIAL in PostgreSQL), you should simply omit them from your INSERT statement's column list. The database will handle it for you.

Let's watch a quick video that shows an INSERT into a table with an auto-incrementing ID and a NOT NULL constraint.

CREATE TABLE products (
    product_id INT PRIMARY KEY, -- (Assume this auto-increments for this question)
    product_code CHAR(10) UNIQUE NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT DEFAULT 0
);

Part 3: Inserting Multiple Rows

Often, you'll need to insert many rows at once. You could execute multiple INSERT statements, but this is inefficient. A much better way is to use a single INSERT statement with multiple value tuples.

The syntax is a simple extension of what you've learned:

INSERT INTO table_name (column1, column2)
VALUES
    (value1a, value2a),
    (value1b, value2b),
    (value1c, value2c);

This is not just cleaner to write; it's also significantly more performant. A single multi-row INSERT statement reduces network latency and transaction overhead compared to many single-row INSERTs.

This graph shows the performance difference when loading one million rows. Notice how performance improves dramatically as you increase the number of rows per INSERT statement (up to an optimal point).

Now, let's read about this syntax and see it in action.

Conclusion

You've now mastered the INSERT statement, a cornerstone of database interaction. You can add single or multiple rows of data, and you understand how these operations interact with the table structure and constraints you've defined.

Key Takeaways:

• The INSERT statement is used to add new rows to a table.
• It's a best practice to always explicitly list the columns you are inserting data into for clarity and robustness.
• INSERT statements must respect all table constraints (NOT NULL, UNIQUE, CHECK). Columns with DEFAULT values or auto-incrementing keys can often be omitted.
• Inserting multiple rows with a single INSERT statement is far more efficient than running multiple separate statements.

Preview of the Next Lesson:
Data is rarely static. Now that you can add records to your tables, the next logical step is to learn how to modify existing records. In the next lesson, we will cover the UPDATE statement, which allows you to change data that is already in your database.