Adding multiple records

You can use the INSERT command to add many records from one table to another. For example, if there was a table called NewEmployee, with an identical structure (field list) to the Employee table, you could add all the records from NewEmployee to Employee using the following statement:

INSERT INTO Employee SELECT * FROM NewEmployee;

If the two tables are not identical, or you only want to append certain fields, list the fields for both source and destination table. For example, you want to add records to the Employee table from an old Employee table containing different field names:

INSERT INTO Employee (EmployeeID, FirstName, LastName) SELECT (EmpID, Fname, Lname) FROM OldEmployee;

Note the SELECT part of the INSERT statement follows all the rules for general SELECT statements described above. For example, you can specify which records to append using the WHERE clause:

INSERT INTO Employee (EmployeeID, FirstName, LastName) SELECT (EmpID, Fname, Lname) FROM OldEmployee WHERE JoinDate < "1/1/80";