oracle 使用一个插入命令插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4152037/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
inserting multiple rows with one insert command
提问by trinity
Is it possible to insert more than one row in a table with one insert statement? I know this will happen if I do:
是否可以使用一个插入语句在表中插入多于一行？我知道如果我这样做会发生这种情况：
insert into table ( fields ) select values from another_table
But what if I want to insert:
但是如果我想插入怎么办：
row 1 - ( a1, b1, c1 )
row 2 - ( a2, b2, c2 )
...
row n - ( an, bn, cn )
with just one insert command?
只有一个插入命令？
回答by Lo?c Février
Two solutions (source : http://appsfr.free.fr/spip.php?article21):
两种解决方案（来源：http: //appsfr.free.fr/spip.php?article21）：
INSERT ALL
INTO table (column1, column2)
VALUES (value1, value2)
INTO table (column1, column2)
VALUES (value1, value2)
...etc...
SELECT * FROM DUAL ;
or
或者
INSERT INTO table (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value1, value2 FROM DUAL UNION ALL
...etc...
SELECT value1, value2 FROM DUAL ;
回答by Ahmet Kak?c?
INSERT ALL
INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;
回答by Sébastien Clément
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);
回答by Godswill Mbata
INSERT INTO College (CustomerID, FirstName, MiddleName, LastName)
SELECT 7, N'Charles', N'Simmons', N'Burns'
UNION ALL
SELECT 9, N'Dominic', N'Fred', N'Einsten'
UNION ALL
SELECT 12, N'Dave', N'William, N'Bryan';
NOTE:Letter N before each hard coded string value converts string to an NVARCHAR value to match the datatype of the column.
注意：每个硬编码字符串值之前的字母 N 将字符串转换为 NVARCHAR 值以匹配列的数据类型。
回答by Mass Nerder
No, this is not possible. As you already stated yourself, it is only possible with a select clause providing the insert values and rows.
不，这是不可能的。正如您自己已经说过的那样，只能使用 select 子句提供插入值和行。
