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.
NOTE: Be sure to save the next three
queries - you may need them again!
Exercise:
Add two records to the Personnel table, one with all the data, the other
with required columns only (two separate queries). Try to add a third record using one of the
StaffIDs already in the table. What
happens? Use these SQL statements for
the first two staff members:
INSERT INTO Personnel
VALUES("7777777",
"Vagabond", "Johnny", #7/17/1950#, "Chemistry");
INSERT INTO Personnel (StaffID, LastName, FirstName)
VALUES ("5555555", "Miller",
"Dusty");
NOTE:
Date/time data type delimited with # in Access; with quotes in other databases.
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