Inserting Data into Tables

Understanding Data Insertion

Up to this point, you have focused exclusively on retrieving and analyzing data that already exists within a database using the SELECT statement. However, an essential part of database management involves populating tables with new information. In standard SQL, the INSERT statement is the primary mechanism used to add new rows of data into a database table.

While data retrieval focuses on extracting information without altering the underlying structures, the INSERT statement physically modifies the database by permanently adding new records. Because of this, you must pay careful attention to the specific data types and constraints defined for each column to avoid execution errors.

Inserting Complete Rows

The most fundamental way to use the INSERT statement is to provide a specific value for every column in a new row. To ensure absolute accuracy and maintainability in your SQL code, it is a strict best practice to explicitly list the column names into which you are inserting data.

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (101, 'Janet', 'Smith', 'jsmith@example.com');

When you provide the column names inside parentheses immediately following the table name, the database engine expects the VALUES list to match that exact sequence. If you specify the first name and then the last name in the column list, you must provide the first name and then the last name in the values list.

Furthermore, you must adhere to standard SQL formatting rules regarding data types:

• Text strings and character data must be enclosed in single quotes.
• Date and time values must also be enclosed in single quotes and follow the required date format.
• Numerical values, such as integers and decimals, must not be enclosed in quotes.

Inserting Partial Rows

You do not always need to provide a value for every single column in a table when adding a new record. If a table definition permits missing values (represented by NULL) or if the database is configured to generate a default value automatically for a specific column, you can safely omit those columns from your INSERT statement entirely.

INSERT INTO customers (customer_id, last_name)
VALUES (102, 'Reynolds');

In this scenario, the database engine will insert the explicitly provided identification number and last name. Any omitted columns, such as the first name or email address, will either be populated with their predefined default values or be marked as NULL. However, if you attempt to omit a column that strictly requires a value and lacks a default constraint, the database engine will return a syntax error and the row will not be inserted.

Inserting Multiple Rows

Modern relational database management systems allow you to insert multiple rows of data using a single, unified INSERT statement. This approach is significantly more efficient than executing multiple individual insert commands, as it reduces network traffic and processing overhead. You achieve this by providing multiple sets of values, separated by commas.

INSERT INTO customers (customer_id, first_name, last_name)
VALUES (103, 'Lee', 'Myers'),
       (104, 'Kathleen', 'Roush'),
       (105, 'David', 'King');

Inserting Retrieved Data

SQL provides a powerful mechanism to copy data from an existing table and insert it directly into another table. Instead of manually typing out a VALUES list, you replace it entirely with a SELECT statement. This technique is known as an INSERT SELECT operation.

INSERT INTO archived_customers (customer_id, first_name, last_name)
SELECT customer_id, first_name, last_name
FROM customers
WHERE status = 'Inactive';

The database engine executes the embedded SELECT statement first, filtering and sorting the data as instructed. It then takes the resulting rows and seamlessly inserts them into the target table. The column names in the source table do not need to perfectly match the column names in the target table; the database relies solely on the sequential order of the columns to map the extracted data correctly.

Apply these data insertion techniques to accurately populate your database tables with new records before moving on to modifying existing data.