插入... 值(SELECT ... FROM ...)

我正在尝试使用另一个表的输入将表INSERT INTO到表中。尽管对于许多数据库引擎来说这是完全可行的,但我似乎总是很难记住当今SQL引擎( MySQLOracleSQL ServerInformixDB2 )的正确语法。

是否有来自 SQL 标准(例如SQL-92 )的 Silver-bullet 语法,该语法可让我插入值而无需担心基础数据库?

答案

尝试:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

这是标准的 ANSI SQL,适用于任何 DBMS

它绝对适用于:

  • 甲骨文
  • MS SQL 服务器
  • 的 MySQL
  • Postgres
  • SQLite v3
  • Teradata
  • DB2
  • Sybase 公司
  • Vertica
  • 数据库
  • H2
  • AWS RedShift
  • SAP HANA

@ Shadow_x99 :应该可以,并且您也可以有多个列和其他数据:

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

编辑:我应该提到,我只在 Access,SQL 2000/2005 / Express,MySQL 和 PostgreSQL 中使用了这种语法,因此应该将它们包括在内。评论者指出它将与 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))

我看到的两个答案都特别适合在 Informix 中使用,并且基本上都是标准 SQL。即表示法:

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

与 Informix 以及所有 DBMS 配合使用都很好。 (从 5 年前开始,MySQL 并不总是支持这种东西;现在,它已经对这种标准 SQL 语法提供了不错的支持,并且 AFAIK 可以在这种表示法上正常工作。)列列表是可选的,但按顺序指示目标列,因此 SELECT 结果的第一列将进入列出的第一列,依此类推。在没有列列表的情况下,SELECT 结果的第一列将进入目标表的第一列。

系统之间可能会有所不同的是用于标识不同数据库中的表的表示法 - 该标准对数据库间(更不用说 DBMS 间)操作了无话可说。使用 Informix,可以使用以下表示法来标识表:

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

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

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

所有者通常不需要被报价;但是,如果确实使用引号,则需要正确拼写所有者名称 - 区分大小写。那是:

someone.table
"someone".table
SOMEONE.table

都标识同一张表。使用 Informix,使用 MODE ANSI 数据库会有一个复杂的问题,其中所有者名称通常会转换为大写(informix 是例外)。也就是说,在 MODE ANSI 数据库(不常用)中,您可以编写:

CREATE TABLE someone.table ( ... )

并且系统目录中的所有者名称将为 “SOMEONE”,而不是 “someone”。如果将所有者名称用双引号引起来,则其作用类似于分隔标识符。使用标准 SQL,分隔符可以在很多地方使用。使用 Informix,您只能在所有者名称周围使用它们 - 在其他情况下,Informix 会将单引号和双引号字符串都视为字符串,而不是将单引号字符串和字符串分开以及将双引号字符串作为分隔标识符。 (当然,出于完整性考虑,可以将环境变量 DELIMIDENT 设置为任意值,但 Y 最安全 - 表示双引号始终包围定界标识符,单引号始终包围字符串。)

请注意,MS SQL Server 设法使用方括号中包含的 [定界标识符]。在我看来,这很奇怪,并且肯定不是 SQL 标准的一部分。

为了在第一个答案中添加一些内容,当我们只希望从另一个表中获取很少的记录时(在此示例中,仅一个):

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

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

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

我使用的每个数据库都遵循以下语法,即DB2SQL ServerMY SQLPostgresQL

代替INSERT查询的VALUES部分,只需使用SELECT查询,如下所示。

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

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

假设 table1 有两列。此查询应工作:

INSERT INTO table1
SELECT  col1, col2
FROM    table2

这将不起作用(未指定col2值):

INSERT INTO table1
SELECT  col1
FROM    table2

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

这是另一个将值与 select 结合使用的示例:

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

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

  1. 使用 SELECT 子查询,返回结果为1 行
  2. 使用 SELECT 子查询,返回多行结果。

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

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

在这种情况下,假定 SELECT 子查询仅基于 WHERE 条件或 SQL 聚合函数(如 SUM,MAX,AVG 等)返回仅一行结果。否则将引发错误

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

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

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