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


          warning


Click Yes to complete the execution step.

Try to add a third row using one of the StaffIDs already in the table.  What happens? 


Verify the results by selecting all data from the Personnel table.  For the statements above:


         personnel data


                   
  

           

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 = "USA";

 

 

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