SQL 插入 ... 值（ SELECT ... FROM ... ）

Insert into ... values ( SELECT ... FROM ... )

提问by Claude Houle

I am trying to INSERT INTOa table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQLengine of the day (MySQL, Oracle, SQL Server, Informix, and DB2).

我正在尝试INSERT INTO使用来自另一个表的输入来创建一个表。尽管这对于许多数据库引擎来说是完全可行的，但我似乎总是很难记住当今SQL引擎（MySQL、Oracle、SQL Server、Informix和DB2）的正确语法。

Is there a silver-bullet syntax coming from an SQL standard (for example, SQL-92) that would allow me to insert the values without worrying about the underlying database?

是否有来自 SQL 标准（例如，SQL-92）的银弹语法允许我插入值而不用担心底层数据库？

回答by Claude Houle

Try:

尝试：

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2  

This is standard ANSI SQL and should work on any DBMS

这是标准的 ANSI SQL，应该适用于任何 DBMS

It definitely works for:

它绝对适用于：

• Oracle
• MS SQL Server
• MySQL
• Postgres
• SQLite v3
• Teradata
• DB2
• Sybase
• Vertica
• HSQLDB
• H2
• AWS RedShift
• SAP HANA

• 甲骨文
• 微软 SQL 服务器
• MySQL
• Postgres
• SQLite v3
• Teradata
• 数据库2
• 赛贝斯
• 维蒂卡
• 数据库
• H2
• AWS 红移
• SAP HANA

回答by travis

Claude Houle's answer: should work fine, and you can also have multiple columns and other data as well:

Claude Houle 的回答：应该可以正常工作，您还可以拥有多个列和其他数据：

INSERT INTO table1 ( column1, column2, someInt, someVarChar )
SELECT  table2.column1, table2.column2, 8, 'some string etc.'
FROM    table2
WHERE   table2.ID = 7;

I've only used this syntax with Access, SQL 2000/2005/Express, MySQL, and PostgreSQL, so those should be covered. It should also work with SQLite3.

我只在 Access、SQL 2000/2005/Express、MySQL 和 PostgreSQL 中使用过这种语法，因此应该涵盖这些内容。它也应该与 SQLite3 一起使用。

回答by kylie.a

To get only one value in a multi value INSERTfrom another table I did the following in SQLite3:

为了INSERT从另一个表中仅获取多值中的一个值，我在 SQLite3 中执行了以下操作：

INSERT INTO column_1 ( val_1, val_from_other_table ) 
VALUES('val_1', (SELECT  val_2 FROM table_2 WHERE val_2 = something))

回答by Jonathan Leffler

Both the answers I see work fine in Informix specifically, and are basically standard SQL. That is, the notation:

我看到的两个答案都特别适用于 Informix，并且基本上都是标准 SQL。也就是说，符号：

INSERT INTO target_table[(<column-list>)] SELECT ... FROM ...;

works fine with Informix and, I would expect, all the DBMS. (Once upon 5 or more years ago, this is the sort of thing that MySQL did not always support; it now has decent support for this sort of standard SQL syntax and, AFAIK, it would work OK on this notation.) The column list is optional but indicates the target columns in sequence, so the first column of the result of the SELECT will go into the first listed column, etc. In the absence of the column list, the first column of the result of the SELECT goes into the first column of the target table.

与 Informix 一起工作得很好，我希望，所有的 DBMS。（曾几何时 5 年或更久以前，这是 MySQL 并不总是支持的那种东西；它现在对这种标准 SQL 语法有很好的支持，而且，AFAIK，它可以在这种表示法上正常工作。）列列表是可选的，但按顺序指示目标列，因此SELECT结果的第一列将进入第一个列出的列，依此类推。在没有列列表的情况下，SELECT结果的第一列进入目标表的第一列。

What can be different between systems is the notation used to identify tables in different databases - the standard has nothing to say about inter-database (let alone inter-DBMS) operations. With Informix, you can use the following notation to identify a table:

系统之间的不同之处在于用于标识不同数据库中的表的符号 - 该标准没有涉及数据库间（更不用说 DBMS 间）操作。使用 Informix，您可以使用以下表示法来标识表：

[dbase[@server]:][owner.]table

That is, you may specify a database, optionally identifying the server that hosts that database if it is not in the current server, followed by an optional owner, dot, and finally the actual table name. The SQL standard uses the term schema for what Informix calls the owner. Thus, in Informix, any of the following notations could identify a table:

也就是说，您可以指定一个数据库，如果它不在当前服务器中，则可以选择标识托管该数据库的服务器，然后是可选的所有者、点，最后是实际的表名。SQL 标准使用术语模式来表示 Informix 所称的所有者。因此，在 Informix 中，以下任何符号都可以标识表：

table
"owner".table
dbase:table
dbase:owner.table
dbase@server:table
dbase@server:owner.table

The owner in general does not need to be quoted; however, if you do use quotes, you need to get the owner name spelled correctly - it becomes case-sensitive. That is:

所有者一般不需要被引用；但是，如果您确实使用引号，则需要正确拼写所有者名称 - 它区分大小写。那是：

someone.table
"someone".table
SOMEONE.table

all identify the same table. With Informix, there's a mild complication with MODE ANSI databases, where owner names are generally converted to upper-case (informix is the exception). That is, in a MODE ANSI database (not commonly used), you could write:

都标识同一个表。对于 Informix，MODE ANSI 数据库有一个轻微的复杂性，其中所有者名称通常转换为大写（informix 是例外）。也就是说，在 MODE ANSI 数据库（不常用）中，您可以编写：

CREATE TABLE someone.table ( ... )

and the owner name in the system catalog would be "SOMEONE", rather than 'someone'. If you enclose the owner name in double quotes, it acts like a delimited identifier. With standard SQL, delimited identifiers can be used many places. With Informix, you can use them only around owner names -- in other contexts, Informix treats both single-quoted and double-quoted strings as strings, rather than separating single-quoted strings as strings and double-quoted strings as delimited identifiers. (Of course, just for completeness, there is an environment variable, DELIMIDENT, that can be set - to any value, but Y is safest - to indicate that double quotes always surround delimited identifiers and single quotes always surround strings.)

并且系统目录中的所有者名称将是“SOMEONE”，而不是“someone”。如果用双引号将所有者名称括起来，它的作用就像一个分隔标识符。使用标准 SQL，可以在很多地方使用分隔标识符。使用 Informix，您只能在所有者名称周围使用它们 —— 在其他上下文中，Informix 将单引号和双引号字符串都视为字符串，而不是将单引号字符串作为字符串和双引号字符串作为分隔标识符进行分隔。（当然，为了完整起见，有一个环境变量 DELIMIDENT，可以设置为任何值，但 Y 是最安全的 - 表示双引号始终围绕分隔标识符，单引号始终围绕字符串。）

Note that MS SQL Server manages to use [delimited identifiers] enclosed in square brackets. It looks weird to me, and is certainly not part of the SQL standard.

请注意，MS SQL Server 设法使用方括号括起来的 [分隔标识符]。这对我来说看起来很奇怪，而且肯定不是 SQL 标准的一部分。

回答by Weslor

To add something in the first answer, when we want only few records from another table (in this example only one):

要在第一个答案中添加一些内容，当我们只需要另一个表中的几条记录（在本例中只有一条）时：

INSERT INTO TABLE1
(COLUMN1, COLUMN2, COLUMN3, COLUMN4) 
VALUES (value1, value2, 
(SELECT COLUMN_TABLE2 
FROM TABLE2
WHERE COLUMN_TABLE2 like "blabla"),
value4);

回答by logan

Instead of VALUESpart of INSERTquery, just use SELECTquery as below.

而不是查询的VALUES一部分INSERT，只需使用SELECT如下查询。

INSERT INTO table1 ( column1 , 2, 3... )
SELECT col1, 2, 3... FROM table2

回答by Santhosh

Most of the databases follow the basic syntax,

大多数数据库都遵循基本语法，

INSERT INTO TABLE_NAME
SELECT COL1, COL2 ...
FROM TABLE_YOU_NEED_TO_TAKE_FROM
;

Every database I have used follow this syntax namely, DB2, SQL Server, MY SQL, PostgresQL

我使用的每个数据库都遵循此语法，即DB2, SQL Server, MY SQL,PostgresQL

回答by Mohammed Safeer

Two approaches for insert into with select sub-query.

使用 select 子查询插入的两种方法。

1. With SELECT subquery returning results with One row.
2. With SELECT subquery returning results with Multiple rows.

1. 随着 SELECT 子查询返回结果为One row。
2. 使用 SELECT 子查询返回多行结果。

1. Approach for With SELECT subquery returning results with one row.

1. With SELECT 子查询返回一行结果的方法。

INSERT INTO <table_name> (<field1>, <field2>, <field3>) 
VALUES ('DUMMY1', (SELECT <field> FROM <table_name> ),'DUMMY2');

In this case, it assumes SELECT Sub-query returns only one row of result based on WHERE condition or SQL aggregate functions like SUM, MAX, AVG etc. Otherwise it will throw error

在这种情况下，它假设 SELECT 子查询根据 WHERE 条件或 SQL 聚合函数（如 SUM、MAX、AVG 等）仅返回一行结果，否则将抛出错误

2. Approach for With SELECT subquery returning results with multiple rows.

2. With SELECT 子查询返回多行结果的方法。

INSERT INTO <table_name> (<field1>, <field2>, <field3>) 
SELECT 'DUMMY1', <field>, 'DUMMY2' FROM <table_name>;

The second approach will work for both the cases.

第二种方法适用于这两种情况。

回答by northben

This can be done without specifying the columns in the INSERT INTOpart if you are supplying values for all columns in the SELECTpart.

INSERT INTO如果您为部件中的所有列提供值，则无需指定部件中的列即可完成此操作SELECT。

Let's say table1 has two columns. This query should work:

假设 table1 有两列。此查询应该有效：

INSERT INTO table1
SELECT  col1, col2
FROM    table2

This WOULD NOT work (value for col2is not specified):

这行不通（col2未指定值）：

INSERT INTO table1
SELECT  col1
FROM    table2

I'm using MS SQL Server. I don't know how other RDMS work.

我正在使用 MS SQL Server。我不知道其他 RDMS 是如何工作的。

回答by Sarvar Nishonboev

This is another example using values with select:

这是另一个使用 select 值的示例：

INSERT INTO table1(desc, id, email) 
SELECT "Hello World", 3, email FROM table2 WHERE ...