ORACLE:基于单个列值插入多条记录
ORACLE:基于单个列值插入多条记录
提问于 2018-09-18 02:40:22
根据Table_1中一列的值(COLUMN_NAME)，我想在Table_2中插入3条新记录。最好的方法是什么？我已经尝试过了，如下所示，但我不想使用UNION ALL进行三个查询
SQL> DROP TABLE TABLE_2
Table dropped.
SQL> CREATE TABLE TABLE_2(EMP_ID VARCHAR2(10),VALUE VARCHAR2(10))
Table created.
SQL> TRUNCATE TABLE TABLE_2
Table truncated.
SQL> INSERT INTO TABLE_2
(EMP_ID,VALUE)
WITH TABLE_1 as
(
SELECT '111' AS EMP_ID,'COL1' COLUMN_NAME FROM DUAL
)
SELECT EMP_ID,CASE WHEN COLUMN_NAME = 'COL1' THEN 'RICK' END AS VALUE
FROM TABLE_1
UNION ALL
SELECT EMP_ID,CASE WHEN COLUMN_NAME = 'COL1' THEN 'TOM' END AS VALUE
FROM TABLE_1
UNION ALL
SELECT EMP_ID,CASE WHEN COLUMN_NAME = 'COL1' THEN 'ADAM' END AS VALUE
FROM TABLE_1
COMMIT
3 rows created.
SQL> SELECT * FROM TABLE_2
EMP_ID VALUE
---------- ----------
111 RICK
111 TOM
111 ADAM
3 rows selected.回答 1
Stack Overflow用户
回答已采纳
发布于 2018-09-18 03:29:45
您可以使用INSERT ALL
INSERT ALL
INTO TABLE_2(EMP_ID,VALUE) VALUES (EMP_ID, 'Rick')
INTO TABLE_2(EMP_ID,VALUE) VALUES (EMP_ID, 'Tom')
INTO TABLE_2(EMP_ID,VALUE) VALUES (EMP_ID, 'Adam')
SELECT '111' AS EMP_ID,'COL1' COLUMN_NAME FROM DUAL;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52373917
复制相关文章
相似问题