Basic Queries  «Prev  Next»

Lesson 6 Using INSERT
Objective Insert new rows into a database table.

SQL Insert Statement

The UPDATE statement is used to change existing rows in a table. In this lesson, you will see how new rows are added.
The INSERT statement adds a new row into a specified table. To use the INSERT statement you generally specify the data fields and their corresponding values for the row to insert. However, if values for every field are being provided then it is not necessary to specify the data fields. In this case, the values are added to the fields from left to right, beginning with the first field. If values for only some fields are being provided then the data fields are specified, unless the values are to go into the first fields as they appear in the row. The syntax for the INSERT statement is illustrated in the following SlideShow:

INSERT statement begins by specifying the table name to insert the row into
1) 
INSERT INTO CustTable
INSERT statement begins by specifying the table name to insert the row into

You can optionally supply a list of column names in parentheses that will be assigned values in the new row. If the column names are omitted, the values in the column list will be entered in the order as the columns appear in the table. 
(column1, column2)
You can optionally supply a list of column names in parentheses that will be assigned values in the new row. If the column names are omitted, the values in the column list will be entered in the order as the columns appear in the table. If you are including values for every field then it is not necessary to specify the data fields.

The VALUES clause contains the column values to assign when the row is inserted. 
VALUES
The VALUES clause contains the column values to assign when the row is inserted.

A list of values is provided in parentheses. If column names were specified, then the values in the VALUE clause will be assigned to the columns in the order they were specified following INTO. 
(value1, value2)
A list of values is provided in parentheses. If column names were specified, then the values in the VALUE clause will be assigned to the columns in the order they were specified following INTO.

The complete insert statement. Let us look at a statement using the BigBook.com database. The following statement inserts a row into the CustTable table. Data is assigned to only three columns: 1) customer number, 2) last name, and 3) first name. 
INSERT INTO table
(column1, column2)
VALUES (value1, value2)
The complete insert statement. Let us look at a statement using the BigBook.com database. The following statement inserts a row into the CustTable table. Data is assigned to only three columns: 1) customer number, 2) last name, and 3) first name.

CustTable table is specified 
CustTable table is specified

Then, you add the column names which will be assigned values 
INSERT INTO CustTable
(CustNo, LName, FName)
Then, you add the column names which will be assigned values

Finally, the values are added. Now you have the complete statement to insert a row. 
INSERT INTO CustTable
(CustNO, LName, FName)
VALUES
('0000000010', 'Rogers', 'William')
Finally, the values are added. Now you have the complete statement to insert a row.

Here are two SQL INSERT statements to add items from a food cabinet into a refrigerator table, based on your explanation of the INSERT syntax:
First Form: Without Specifying Column Names 
INSERT INTO refrigerator
 VALUES ('Milk', 'Dairy', '2024-12-01', 1),
        ('Eggs', 'Dairy', '2024-11-25', 12),
        ('Strawberry Jam', 'Condiments', '2025-06-15', 1),
        ('Yogurt', 'Dairy', '2024-11-30', 1);

Second Form: With Column Names Specified Assuming the refrigerator table has columns like `item_name`, `category`, `expiration_date`, and `quantity`: 
INSERT INTO refrigerator (item_name, category, expiration_date, quantity)
 VALUES ('Milk', 'Dairy', '2024-12-01', 1),
        ('Eggs', 'Dairy', '2024-11-25', 12),
        ('Strawberry Jam', 'Condiments', '2025-06-15', 1),
        ('Yogurt', 'Dairy', '2024-11-30', 1);
In these examples:
  • item_name: The name of the food item.
  • category: The category to which the item belongs.
  • expiration_date: The date when the item expires.
  • quantity: How much of the item there is.
When using the first form, you must ensure that the values are in the exact order of the columns as they are defined in the table, which can be error-prone if the table structure changes or if you're not certain about the column order. The second form is generally safer and more maintainable as it explicitly names the columns, making the SQL more readable and less prone to errors if the table structure changes.
In the next lesson, removing rows using the DELETE statement will be discussed.

SQL Insert Statement - Exercise

Click the Exercise link below to practice adding a row to the VirtualBookShelf.com BookTable. 
SQL Insert Exercise

SEMrush Software