The INSERT Statement: Adding Data

So far, you've learned how to ask questions of your database using SELECT and how to refine those questions with WHERE and ORDER BY. These commands are essential for retrieving information. But how does data get into the tables in the first place? That's where the INSERT statement comes in. It's the fundamental SQL command for adding new rows, or records, to a table.

Think of a table like a spreadsheet grid. SELECT reads rows from the grid. INSERT adds entirely new rows to that grid.

Basic Syntax: Adding a Complete Row

The most straightforward way to use INSERT is to provide a value for every column in the table, in the exact order the columns are defined. The basic syntax looks like this:

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

Let's break this down:

1. INSERT INTO table_name: This specifies the target table where you want to add the new row. Replace table_name with the actual name of your table (e.g., Customers, Products).
2. VALUES (value1, value2, value3, ...): This clause provides the actual data for the new row.
   • The values must be enclosed in parentheses ().
   • The values must be listed in the same order as the columns appear in the table definition.
   • You need to provide a value for every column in the table.
   • The data type of each value must match the data type of the corresponding column (e.g., provide a number for an integer column, text within single quotes for a character column).

Example:

Suppose we have a Products table defined like this:

• ProductID (INTEGER, Primary Key)
• ProductName (VARCHAR - i.e., text)
• Price (DECIMAL - i.e., number with decimals)
• StockQuantity (INTEGER)

To add a new product, "Espresso Machine", with ProductID 101, costing 199.99, and 15 units in stock, you would write:

INSERT INTO Products
VALUES (101, 'Espresso Machine', 199.99, 15);

After executing this command, a new row containing this information will be added to the Products table.

Specifying Column Names

Relying on the exact column order can be risky. If the table structure changes later (e.g., columns are reordered or new ones added), your INSERT statement might fail or, worse, insert data into the wrong columns.

A safer and often clearer approach is to explicitly list the columns you want to populate, followed by the corresponding values in the same order.

The syntax is:

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

Here:

1. (column1, column2, column3, ...): You list the specific columns you are providing data for.
2. (value1, value2, value3, ...): You provide the values corresponding to the listed columns. value1 goes into column1, value2 into column2, and so on.

Example:

Using the same Products table, let's add a "Coffee Grinder" costing 45.50 with 30 units in stock. We know the ProductID is the primary key, and often, databases can generate these automatically (we'll touch on this lightly). For now, let's assume we don't need to specify the ProductID or that we want to insert it explicitly but perhaps out of order for demonstration. Specifying columns gives us flexibility.

INSERT INTO Products (ProductName, Price, StockQuantity, ProductID)
VALUES ('Coffee Grinder', 45.50, 30, 102);

Or, if the ProductID is automatically generated by the database (a common feature called auto-increment or identity):

INSERT INTO Products (ProductName, Price, StockQuantity)
VALUES ('Coffee Grinder', 45.50, 30);

In this second case, the database itself would assign the next available ProductID (perhaps 102, if 101 was the last one used).

Using the column list makes your SQL statement more readable and less prone to errors if the table structure changes. You only need to provide values for the columns you list. Any columns not listed will receive their default value (if one is defined) or potentially cause an error if they don't have a default and cannot be empty (like NOT NULL columns). For beginners, it's good practice to list the columns unless you are certain about the column order and are providing all values.

Inserting Multiple Rows

Most database systems allow you to insert multiple rows with a single INSERT statement, which is more efficient than running separate INSERT commands for each row. The syntax typically involves listing multiple sets of values:

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

Example:

Adding two more products to our Products table (assuming ProductID is auto-generated):

INSERT INTO Products (ProductName, Price, StockQuantity)
VALUES
  ('Milk Frother', 25.00, 50),
  ('Pour Over Kettle', 60.75, 22);

This single statement adds two distinct rows to the Products table.

The INSERT statement is your tool for populating tables. Whether you provide values for all columns implicitly by order or explicitly list the columns, you are adding new, complete records to your database. Remember to match data types and be mindful of column order if you aren't specifying column names.