Insert into … values ( SELECT … FROM … )

技术背景

在数据库开发中，经常需要将一个表中的数据插入到另一个表中。INSERT INTO ... SELECT ... FROM ... 语句是实现这一功能的常用方法，它是标准的ANSI SQL语法，适用于多种数据库管理系统（DBMS）。

实现步骤

单表数据插入

插入单值

INSERT INTO table1 ( column1 )
SELECT  col1
FROM  table2;

此语句将 table2 中的 col1 列的数据插入到 table1 的 column1 列。

插入多值

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

该语句将 table2 中满足 ID = 7 条件的 column1 和 column2 列的数据，以及固定值 8 和 'some string etc.' 插入到 table1 相应的列中。

处理子查询结果

子查询返回单行结果

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

此语句假设子查询基于 WHERE 条件或 SQL 聚合函数（如 SUM、MAX、AVG 等）只返回一行结果，否则会抛出错误。

子查询返回多行结果

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

该方法适用于子查询返回单行或多行结果的情况。

多表数据插入

insert into StudentCourseMap (StudentId, CourseId) 
SELECT  Student.Id, Course.Id FROM Student, Course 
WHERE Student.Name = 'Paddy Murphy' AND Course.Name = 'Basket weaving for beginners';

此语句从 Student 和 Course 表中选择满足条件的数据插入到 StudentCourseMap 表中。

核心代码

常见数据库通用语法

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

插入特定条件数据

INSERT INTO archived_employees (id, name, department, salary)
SELECT id, name, department, salary FROM employees
WHERE department = 'Sales';

最佳实践

• 指定列名：在 INSERT INTO 语句中明确指定列名，避免因表结构变化而导致插入失败。
• 检查子查询结果：在使用子查询时，确保子查询返回的结果符合预期，避免因返回多行结果而导致错误。
• 使用事务：在进行大量数据插入时，使用事务可以确保数据的一致性和完整性。

常见问题

列数不匹配

如果 INSERT 语句中的列数与 SELECT 语句返回的列数不匹配，会导致错误。例如：

INSERT INTO table1
SELECT  col1
FROM    table2

如果 table1 有多个列，而 SELECT 只返回了一个列，就会出现列数不匹配的问题。解决方法是确保 INSERT 和 SELECT 的列数和数据类型一致。

子查询返回多行结果

当子查询返回多行结果，但使用了 VALUES 语句时，会抛出错误。应使用 SELECT 语句来处理多行结果。

不同数据库的语法差异

不同的数据库对 INSERT ... SELECT 语句可能有细微的差异。例如：

• SQL Server 和 Oracle：如果 SELECT 的列与 INSERT 的列不匹配，需要显式指定列列表。
• Oracle：对于多行插入可能需要使用 INSERT ALL。
• DB2：可能需要根据查询优化器进行调整。