After you create a table, you can insert records into it using INSERT statements. Each INSERT statement inserts a single record into a table. Look at the following syntax for the INSERT statement:
INSERT INTO Tablename [(ColumnNames, ...)] VALUES (values, ...);
Each INSERT statement contains the INSERT INTO and VALUES keywords. The INSERT INTO keywords are used to specify the table name and the column names to insert values into. The VALUES keyword is used to specify the values to insert into a table. Take a look at Example-E, which inserts five rows into the Toys table.
This example inserts five records into the Toys table created earlier in the tutorial.
INSERT INTO Toys (ToyID, ToyName, Price, Description) VALUES (1, 'ToyTrain1', 11.00, 'Red/blue battery powered train'); INSERT INTO Toys (ToyID, ToyName, Price, Description) VALUES (2, 'ToyTrain2', 11.00, 'Green/red/blue battery powered train'); INSERT INTO Toys (ToyID, ToyName, Price, Description) VALUES (3, 'ElectricTrain', 15.00, 'Red/white AC/DC powered train'); INSERT INTO Toys (ToyID, ToyName, Price, Description) VALUES (4, 'LivingDoll1', 12.00, 'Asian American Doll'); INSERT INTO Toys (ToyID, ToyName, Price, Description) VALUES (5, 'LivingDoll2', 12.00, 'African American Doll');
The preceding INSERT statements insert five records into the Toys table. Since each INSERT statement contains a closing semicolon, it is easy to see where each statement begins and ends. Each INSERT statement inserts one record with four values.
As a side note, each time you execute an INSERT statement, Microsoft Access verifies the insertion of the new record by displaying a message/question that says:
"You are about to append 1 row (s). Once you click yes, you can't use the undo command to reverse the changes. Are you sure you want to append the selected rows?" This feature can be turned off under Access options | Advanced | Confirm Action Queries, but for now leave it on as it will verify the actions taking place.
Each time you insert a new record, be sure to click Yes to this message.
Notice the INSERT statements that contain character strings enclosed in quotes. Whenever a table contains a column data type that accepts character strings, all character string values pertaining to the column must be enclosed in quotes. Since the ToyName and Description columns contain data types that accept character strings, the character string values in the INSERT statements are enclosed in quotes.
Type the following script to view the populated Toys table:
SELECT * FROM Toys;
In this tutorial:
- Creating Tables and Inserting Records
- Data Definition Language Component
- CREATE TABLE Syntax
- SQL Data Types
- NULL/NOT NULL Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
- Adding Constraints to Existing Tables
- Constraint Syntax
- Inserting Records
- Inserting Data without Specifying Column Names