sql-insert-into-select

SQL INSERT INTO SELECT 语句

INSERT INTO SELECT 语句是一个强大的命令，它从一个表复制数据并将其插入到另一个表。这对于归档旧数据、创建汇总表或在 ETL（Extract, Transform, Load，提取、转换、加载）过程中填充暂存表等任务非常有用。

关键要求是源 SELECT 语句中的数据类型必须与目标表中列的数据类型兼容。

语法

使用此语句主要有两种方式：

1. 插入所有列

如果要将数据插入到目标表的所有列中，并且源表的列顺序相同且数据类型兼容，可以使用简单的 SELECT *。

INSERT INTO target_table
SELECT * FROM source_table
WHERE condition;

2. 插入特定列

更常见的情况是，您会指定要插入哪些列以及从哪些源列中提取数据。这种方式更健壮，因为它不依赖于列的顺序。

INSERT INTO target_table (column1, column2, column3)
SELECT source_column1, source_column2, source_column3
FROM source_table
WHERE condition;

实际应用：归档旧订单

假设您有一个 Orders 表，它变得非常大。您想将所有在 2022 年之前完成的订单移动到 Orders_Archive 表中，以提高主 Orders 表的查询性能。

-- 假设 'Orders' 和 'Orders_Archive' 具有相同的结构：
-- (OrderID, CustomerID, OrderDate, TotalAmount, Status)

-- 步骤 1：将旧订单复制到归档表
INSERT INTO Orders_Archive (OrderID, CustomerID, OrderDate, TotalAmount, Status)
SELECT OrderID, CustomerID, OrderDate, TotalAmount, Status
FROM Orders
WHERE OrderDate < '2022-01-01';

-- 步骤 2（注意！）：确认复制成功后，从主表中删除旧订单。
-- 务必将其包装在一个事务中。
BEGIN TRANSACTION;

DELETE FROM Orders
WHERE OrderDate < '2022-01-01';

-- 如果两个步骤都成功，则提交。
COMMIT;

复制有限数量的行

有时您只需要复制一部分行，例如，用于测试目的。限制行数的语法因 SQL 方言而异。

示例：复制最近 10 个订单

标准 SQL (PostgreSQL, Oracle):

INSERT INTO Sample_Orders
SELECT * FROM Orders
ORDER BY OrderDate DESC
FETCH FIRST 10 ROWS ONLY;

MySQL / MariaDB:

INSERT INTO Sample_Orders
SELECT * FROM Orders
ORDER BY OrderDate DESC
LIMIT 10;

SQL Server:

INSERT INTO Sample_Orders
SELECT TOP (10) * FROM Orders
ORDER BY OrderDate DESC;

注意事项和最佳实践

• 性能：插入大量行可能是一个繁重的操作，会锁定表并占用大量事务日志空间。在大数据移动时应选择流量较低的时段进行。
• 事务：始终将 INSERT INTO SELECT 后跟 DELETE 操作包装在单个事务中，以确保操作的原子性。如果 DELETE 失败，INSERT 可以回滚，防止数据重复。
• 约束和触发器：请注意，在插入过程中，目标表上的约束（如 NOT NULL、UNIQUE）和触发器将被强制执行。对于非常大的批量插入，暂时禁用它们可能会更快，但这应该非常小心地进行，以免损害数据完整性。