如何用单条SQL语句向三张无关联表插入数据？能否插入多表？

嘿，这个问题问得很实用！先给你明确答案：完全可以通过单条SQL语句向多张未建立关联的表插入数据，不过具体写法得看你用的数据库管理系统（DBMS），不同系统的语法差异不小。下面针对主流数据库给出具体示例：

1. Oracle（原生支持多表插入） #

Oracle的INSERT ALL语法就是专门为单条SQL插入多表设计的，非常贴合你的场景。假设我们有三张无关联的表：

• customers(cust_id, cust_name)
• logs(log_id, log_content, log_time)
• products(prod_id, prod_title)

对应的单条插入SQL如下：

INSERT ALL
  INTO customers (cust_id, cust_name) VALUES (1001, 'Bob Smith')
  INTO logs (log_id, log_content, log_time) VALUES (5001, 'New customer added', SYSDATE)
  INTO products (prod_id, prod_title) VALUES (3001, 'Bluetooth Headphones')
SELECT * FROM DUAL;

这里的SELECT * FROM DUAL是Oracle要求的占位查询，用来触发INSERT ALL的执行逻辑。

2. MySQL/MariaDB #

MySQL本身不支持原生的单条INSERT插入多表，但可以通过两种方式实现类似效果：

方式1：事务包裹多条INSERT（保证原子性） #

虽然是多条语句，但可以作为一个请求提交，且满足“要么全成功，要么全失败”的原子性要求：

BEGIN;
INSERT INTO customers (cust_id, cust_name) VALUES (1001, 'Bob Smith');
INSERT INTO logs (log_id, log_content, log_time) VALUES (5001, 'New customer added', NOW());
INSERT INTO products (prod_id, prod_title) VALUES (3001, 'Bluetooth Headphones');
COMMIT;

方式2：WITH子句（MySQL 8.0+支持，严格单条SQL） #

如果需要严格意义上的单条SQL语句，可以用CTE生成数据后分别插入：

WITH insert_data AS (
  SELECT 1001 AS cust_id, 'Bob Smith' AS cust_name,
         5001 AS log_id, 'New customer added' AS log_content, NOW() AS log_time,
         3001 AS prod_id, 'Bluetooth Headphones' AS prod_title
)
INSERT INTO customers (cust_id, cust_name) SELECT cust_id, cust_name FROM insert_data;
INSERT INTO logs (log_id, log_content, log_time) SELECT log_id, log_content, log_time FROM insert_data;
INSERT INTO products (prod_id, prod_title) SELECT prod_id, prod_title FROM insert_data;

3. PostgreSQL #

PostgreSQL允许在单条SQL中执行多个INSERT语句，也可以结合WITH子句复用数据：

方式1：多INSERT单语句提交 #

直接把多个INSERT放在同一个SQL请求里执行，默认会在一个事务中完成：

INSERT INTO customers (cust_id, cust_name) VALUES (1001, 'Bob Smith');
INSERT INTO logs (log_id, log_content, log_time) VALUES (5001, 'New customer added', CURRENT_TIMESTAMP);
INSERT INTO products (prod_id, prod_title) VALUES (3001, 'Bluetooth Headphones');

方式2：WITH子句复用数据 #

如果插入的数据有重复部分，用这种方式更简洁：

WITH data AS (
  SELECT 
    1001 AS cust_id, 'Bob Smith' AS cust_name,
    5001 AS log_id, 'New customer added' AS log_content, CURRENT_TIMESTAMP AS log_time,
    3001 AS prod_id, 'Bluetooth Headphones' AS prod_title
)
INSERT INTO customers SELECT cust_id, cust_name FROM data;
INSERT INTO logs SELECT log_id, log_content, log_time FROM data;
INSERT INTO products SELECT prod_id, prod_title FROM data;

4. SQL Server #

SQL Server的实现逻辑和PostgreSQL类似，支持单条SQL中包含多个INSERT：

多INSERT单语句 #

INSERT INTO customers (cust_id, cust_name) VALUES (1001, 'Bob Smith');
INSERT INTO logs (log_id, log_content, log_time) VALUES (5001, 'New customer added', GETDATE());
INSERT INTO products (prod_id, prod_title) VALUES (3001, 'Bluetooth Headphones');

这些语句会在同一个批处理中执行，默认保证原子性。

WITH子句方式 #

WITH insert_data AS (
  SELECT 
    1001 AS cust_id, 'Bob Smith' AS cust_name,
    5001 AS log_id, 'New customer added' AS log_content, GETDATE() AS log_time,
    3001 AS prod_id, 'Bluetooth Headphones' AS prod_title
)
INSERT INTO customers (cust_id, cust_name) SELECT cust_id, cust_name FROM insert_data;
INSERT INTO logs (log_id, log_content, log_time) SELECT log_id, log_content, log_time FROM insert_data;
INSERT INTO products (prod_id, prod_title) SELECT prod_id, prod_title FROM insert_data;

总结一下：

• Oracle的INSERT ALL是最直接的单条SQL多表插入方案；
• 其他主流数据库可以通过“单请求多INSERT+事务”或者CTE/WITH子句实现需求；
• 无论哪种方式，都能满足向多张未关联表插入数据的要求，关键是匹配你的数据库系统选择语法。

内容的提问来源于stack exchange，提问作者amol