How to Correctly Insert Data from One Table to Another in Microsoft Access using SQL?

Database Query and Data Insertion Guide

In database operations, it is often necessary to extract data from one table and insert it into another table. In Microsoft Access, this is accomplished through a SQL query. However, beginners may encounter syntax errors when writing these queries.

For example, a user attempts to execute the following query:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES
  (SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);

This query is designed to extract and transform data from Table1 and then insert it into Table2. However, the user encountered a syntax error. The solution lies in using the correct syntax, i.e. removing the "VALUES" keyword and brackets:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;

This corrected version will successfully perform the intended task of inserting the extracted and transformed data into Table2. Remember, avoid using "VALUES" and parentheses in SQL queries when inserting data.

The above is the detailed content of How to Correctly Insert Data from One Table to Another in Microsoft Access using SQL?. For more information, please follow other related articles on the PHP Chinese website!