Data
modification
language statements (DML) INSERT, UPDATE, and DELETE
DML - INSERT
Use
the
INSERT command to enter data into a table.
You may insert one row at a time, or select several rows from an
existing table and insert them all at once.
Exercise:
Add two rows to the Personnel table, one with all the data
filled in, the other
with required columns only (two separate queries).
Use
these SQL statements or similar:
INSERT INTO Personnel
VALUES("7777777",
"Smith", "John", #7/17/1950#, "Chemistry");
INSERT INTO Personnel
(StaffID, LastName, FirstName)
VALUES ("5555555",
"Jones",
"Jane");
NOTE:
The date/time data type uses a # delimiter in Access; quotes in other
databases.
When you execute the statements, Access will give you a
warning (other database systems do not!):l
Click Yes to
complete the execution step.
Verify the results by selecting all data from
the Personnel table. For the statements above:
Exercise:
Add several employees from the Northwind database to the
Personnel
table. Here is an SQL statement that
should add 5 staff members:
INSERT INTO Personnel
(StaffID, LastName, FirstName,
Birthday)
SELECT EmployeeID,
LastName, FirstName, BirthDate
FROM Employees
WHERE Country = "
Syntax
Single-row
INSERT:
INSERT INTO tablename
(column list)
VALUES (value list)
Multi-row
INSERT:
INSERT INTO tablename
(column list)
SELECT
column list
FROM othertable
WHERE criteria
Column
and
value lists are comma-separated lists in both cases