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?
You have two syntax options:
Option 1
Option 2
Bear in mind that Option 2 will create a table with only the columns on the projection (those on the SELECT).
Remove both VALUES and the parenthesis.
Well I think the best way would be (will be?) to define 2 recordsets and use them as an intermediate between the 2 tables.
This method is particularly interesting if you plan to update tables from different databases (ie each recordset can have its own connection ...)