How to do INSERT into a table records extracted from another table

sql ms-access
Martin08 picture Martin08 · Sep 16, 2008 · Viewed 663.6k times · Source

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:

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

I tried but get a syntax error message.

What would you do if you want to do this?

Answer

pilsetnieks picture pilsetnieks · Sep 16, 2008

No "VALUES", no parenthesis:

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

Related questions

How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
A select query selecting a select statement

I don't even know if I am doing this query the right way. There is a Sandwiches table that has some 7 fields and 2 of them are comboboxes (Type and Bread). So I made a query that combines all of the …

Read More