How to do INSERT into a table records extracted fr

2019-01-01 10:11发布

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?

标签: sql ms-access
9条回答
若你有天会懂
2楼-- · 2019-01-01 10:47

You have two syntax options:

Option 1

CREATE TABLE Table1 (
    id int identity(1, 1) not null,
    LongIntColumn1 int,
    CurrencyColumn money
)

CREATE TABLE Table2 (
    id int identity(1, 1) not null,
    LongIntColumn2 int,
    CurrencyColumn2 money
)

INSERT INTO Table1 VALUES(12, 12.00)
INSERT INTO Table1 VALUES(11, 13.00)

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

Option 2

CREATE TABLE Table1 (
    id int identity(1, 1) not null,
    LongIntColumn1 int,
    CurrencyColumn money
)

INSERT INTO Table1 VALUES(12, 12.00)
INSERT INTO Table1 VALUES(11, 13.00)


SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1
INTO Table2
FROM Table1
GROUP BY LongIntColumn1

Bear in mind that Option 2 will create a table with only the columns on the projection (those on the SELECT).

查看更多
千与千寻千般痛.
3楼-- · 2019-01-01 10:47

Remove both VALUES and the parenthesis.

INSERT INTO Table2 (LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) FROM Table1 GROUP BY LongIntColumn1
查看更多
其实,你不懂
4楼-- · 2019-01-01 10:48

Well I think the best way would be (will be?) to define 2 recordsets and use them as an intermediate between the 2 tables.

  1. Open both recordsets
  2. Extract the data from the first table (SELECT blablabla)
  3. Update 2nd recordset with data available in the first recordset (either by adding new records or updating existing records
  4. Close both recordsets

This method is particularly interesting if you plan to update tables from different databases (ie each recordset can have its own connection ...)

查看更多
登录 后发表回答