ORACLE开发知识：Oracle 数据仓库ETL技术之多表插入语句的示例详解

CREATE TABLE src_table(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR2(10) NOT NULL
);
INSERT INTO src_table VALUES (1, ‘张三’);
INSERT INTO src_table VALUES (2, ‘李四’);
INSERT INTO src_table VALUES (3, ‘王五’);

CREATE TABLE tgt_t1 AS
SELECT * FROM src_table WHERE 1=0;

CREATE TABLE tgt_t2 AS
SELECT * FROM src_table WHERE 1=0;

CREATE TABLE tgt_t3 AS
SELECT * FROM src_table WHERE 1=0;

INSERT ALL
INTO tgt_t1(id, name) VALUES(id, name)
INTO tgt_t2(id, name) VALUES(id, name)
INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME |
–|——|
1|张三 |
2|李四 |
3|王五 |

SELECT * FROM tgt_t2;
ID|NAME |
–|——|
1|张三 |
2|李四 |
3|王五 |

SELECT * FROM tgt_t3;
ID|NAME |
–|——|
1|张三 |
2|李四 |
3|王五 |

TRUNCATE TABLE tgt_t1;

INSERT ALL
INTO tgt_t1(id, name) VALUES(4, ‘赵六’)
INTO tgt_t1(id, name) VALUES(5, ‘孙七’)
INTO tgt_t1(id, name) VALUES(6, ‘周八’)
SELECT 1 FROM dual;

SELECT * FROM tgt_t1;
ID|NAME |
–|——|
4|赵六 |
5|孙七 |
6|周八 |

— MySQL、SQL Server、PostgreSQL以及SQLite
INSERT INTO tgt_t1(id, name)
VALUES(4, ‘赵六’), (5, ‘孙七’), (6, ‘周八’);

CREATE TABLE src_pivot(
id INTEGER NOT NULL PRIMARY KEY,
name1 VARCHAR2(10) NOT NULL,
name2 VARCHAR2(10) NOT NULL,
name3 VARCHAR2(10) NOT NULL
);
INSERT INTO src_pivot VALUES (1, ‘张三’, ‘李四’, ‘王五’);

TRUNCATE TABLE tgt_t1;

INSERT ALL
INTO tgt_t1(id, name) VALUES(id, name1)
INTO tgt_t1(id, name) VALUES(id, name2)
INTO tgt_t1(id, name) VALUES(id, name3)
SELECT * FROM src_pivot;

SELECT * FROM tgt_t1;
ID|NAME |
–|——|
1|张三 |
1|李四 |
1|王五 |

TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;

INSERT ALL
WHEN id <= 1 THEN
INTO tgt_t1(id, name) VALUES(id, name)
WHEN id BETWEEN 1 AND 2 THEN
INTO tgt_t2(id, name) VALUES(id, name)
ELSE
INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME |
–|——|
1|张三 |

SELECT * FROM tgt_t2;
ID|NAME |
–|——|
1|张三 |
2|李四 |

SELECT * FROM tgt_t3;
ID|NAME |
–|——|
3|王五 |

TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;

INSERT FIRST
WHEN id <= 1 THEN
INTO tgt_t1(id, name) VALUES(id, name)
WHEN id BETWEEN 1 AND 2 THEN
INTO tgt_t2(id, name) VALUES(id, name)
ELSE
INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

SELECT * FROM tgt_t1;
ID|NAME |
–|——|
1|张三 |

SELECT * FROM tgt_t2;
ID|NAME |
–|——|
2|李四 |

SELECT * FROM tgt_t3;
ID|NAME |
–|——|
3|王五 |