Access 101: INSERT INTO ("Append") Query

In the world of Microsoft Access, queries are the workhorses that power your database.

They allow you to retrieve, manipulate, and analyze your data in various ways. Among the different types of queries, the INSERT INTO query, also known as an "Append" query, holds a special place.

This type of query allows you to add new records to a table, making it a vital tool for managing your data.

What is an INSERT INTO Query?

An INSERT INTO query, as its name suggests, is used to insert (or append) data into a table. This type of query is especially useful when you need to add multiple records to a table at once.

Basic Syntax

The basic syntax of an INSERT INTO query in Access is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

In this syntax:

  • table_name is the name of the table where you want to insert data.
  • column1, column2, column3, ... are the names of the columns in the table where you want to insert data.
  • value1, value2, value3, ... are the values that you want to insert into the respective columns.

Example

Let's say we have a table named Employee with the following columns: EmployeeID, FirstName, LastName, and Position. If we want to add a new employee to the table, we can use an INSERT INTO query like this:

INSERT INTO Employee (EmployeeID, FirstName, LastName, Position)
VALUES (101, 'John', 'Doe', 'Manager');

This query will add a new record to the Employee table with an EmployeeID of 101, a FirstName of 'John', a LastName of 'Doe', and a Position of 'Manager'.

INSERT INTO From Another Table

You can also use the INSERT INTO query to insert data from one table into another. The syntax for this is slightly different:

INSERT INTO table1 (column1, column2, column3, ...)
SELECT columnA, columnB, columnC, ...
FROM table2
WHERE condition;

In this syntax, table2 is the name of the table from which you want to select data, and condition is a condition that the records must meet to be selected.  As with the VALUES syntax, the order of the columns listed in the SELECT clause must match up with those in the INSERT INTO clause.  The first column listed in the SELECT clause will be inserted into the table at the first column listed in the INSERT INTO clause.  

The names of the columns need not match; only the order of the columns is important.

Conclusion

The INSERT INTO query is a powerful tool in Access that allows you to add new records to your tables. Whether you're adding a single record or transferring data from one table to another, understanding how to use this type of query is essential for managing your data effectively.

Further Reading

Reformat an INSERT INTO...VALUES Statement with ChatGPT
Let’s use ChatGPT to help us debug an SQL INSERT INTO ... VALUES statement by lining up the fields with the values.

The following article includes a technique for reliably undoing an INSERT INTO ... SELECT ... FROM ... query:

3 Techniques for Programming Reversible Processes
How would you recover from a large accidental UPDATE operation? You’d better be able to answer that question *before* it happens.

Acknowledgements
  • Initial draft generated with the help of ChatGPT
  • Article excerpt generated with the help of ChatGPT
  • One or more code samples generated with the help of ChatGPT
  • Sample data generated with the help of ChatGPT