SQL 如何插入从另一个表中提取的表记录

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

提问by Martin08

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:

我正在尝试编写一个查询，从表中提取和转换数据，然后将这些数据插入到另一个表中。是的，这是一个数据仓库查询，我正在 MS Access 中进行。所以基本上我想要一些这样的查询：

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?

如果你想做这个，你会怎么做？

回答by pilsetnieks

No "VALUES", no parenthesis:

没有“VALUES”，没有括号：

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

回答by Jorge Ferreira

You have two syntax options:

您有两个语法选项：

Option 1

选项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

选项 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).

请记住，选项 2 将创建一个仅包含投影列（SELECT 上的列）的表。

回答by GSerg

Remove both VALUES and the parenthesis.

删除 VALUES 和括号。

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

回答by Forgotten Semicolon

Remove VALUESfrom your SQL.

VALUES从您的 SQL 中删除。

回答by Sean

I believe your problem in this instance is the "values" keyword. You use the "values" keyword when you are inserting only one row of data. For inserting the results of a select, you don't need it.

我相信您在这种情况下的问题是“值”关键字。当您只插入一行数据时，您可以使用“values”关键字。对于插入选择的结果，您不需要它。

Also, you really don't need the parentheses around the select statement.

此外，您真的不需要 select 语句周围的括号。

From msdn:

从msdn：

Multiple-record append query:

多记录追加查询：

INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, …]
FROM tableexpression

Single-record append query:

单记录追加查询：

INSERT INTO target [(field1[, field2[, …]])]     
VALUES (value1[, value2[, …])

回答by Chris OC

Remove "values" when you're appending a group of rows, and remove the extra parentheses. You can avoid the circular reference by using an alias for avg(CurrencyColumn) (as you did in your example) or by not using an alias at all.

在附加一组行时删除“值”，并删除额外的括号。您可以通过为 avg(CurrencyColumn) 使用别名（如您在示例中所做的那样）或根本不使用别名来避免循环引用。

If the column names are the same in both tables, your query would be like this:

如果两个表中的列名相同，则您的查询将如下所示：

INSERT INTO Table2 (LongIntColumn, Junk)
SELECT LongIntColumn, avg(CurrencyColumn) as CurrencyColumn1
FROM Table1
GROUP BY LongIntColumn;

And it would work without an alias:

它可以在没有别名的情况下工作：

INSERT INTO Table2 (LongIntColumn, Junk)
SELECT LongIntColumn, avg(CurrencyColumn)
FROM Table1
GROUP BY LongIntColumn;

回答by Chris OC

inserting data form one table to another table in different DATABASE

将数据从一张表插入到不同数据库中的另一张表

insert into DocTypeGroup 
    Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType 
    from Opendatasource( 'SQLOLEDB','Data Source=10.132.20.19;UserID=sa;Password=gchaturthi').dbIPFMCI.dbo.DocTypeGroup

回答by Philippe Grondier

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

好吧，我认为最好的方法是（将是？）定义 2 个记录集并将它们用作 2 个表之间的中间体。

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

1. 打开两个记录集
2. 从第一个表中提取数据 (SELECT blablabla)
3. 使用第一个记录集中可用的数据更新第二个记录集（通过添加新记录或更新现有记录）
4. 关闭两个记录集

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

如果您计划更新来自不同数据库的表（即每个记录集可以有自己的连接...）

回答by Philippe Grondier

Do you want to insert extraction in an existing table?

您想在现有表中插入提取吗？

If it does not matter then you can try the below query:

如果没关系，那么您可以尝试以下查询：

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

It will create a new table -> T1 with the extracted information

它将使用提取的信息创建一个新表 -> T1