在单个 SQL 查询中插入多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/452859/
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 in a single SQL query?
提问by BinaryMisfit
I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Idand Office.
我有多组数据要一次插入，比如说 4 行。我的表有三列：Person,Id和Office。
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office");
INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office");
INSERT INTO MyTable VALUES ("Billy", 125, "London Office");
INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office");
Can I insert all 4 rows in a single SQL statement?
我可以在一个 SQL 语句中插入所有 4 行吗？
回答by BinaryMisfit
In SQL Server 2008 you can insert multiple rows using a single SQL INSERT statement.
在 SQL Server 2008 中，您可以使用单个 SQL INSERT 语句插入多行。
INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )
For reference to this have a look at MOC Course 2778A - Writing SQL Queries in SQL Server 2008.
如需参考，请查看 MOC 课程 2778A - 在 SQL Server 2008 中编写 SQL 查询。
For example:
例如：
INSERT INTO MyTable
( Column1, Column2, Column3 )
VALUES
('John', 123, 'Lloyds Office'),
('Jane', 124, 'Lloyds Office'),
('Billy', 125, 'London Office'),
('Miranda', 126, 'Bristol Office');
回答by too much php
If you are inserting into a single table, you can write your query like this (maybe only in MySQL):
如果要插入单个表，则可以像这样编写查询（可能仅在 MySQL 中）：
INSERT INTO table1 (First, Last)
VALUES
('Fred', 'Smith'),
('John', 'Smith'),
('Michael', 'Smith'),
('Robert', 'Smith');
回答by DavGarcia
NOTE:This answer is for SQL Server 2005. For SQL Server 2008 and later, there are much better methods as seen in the other answers.
注意：此答案适用于 SQL Server 2005。对于 SQL Server 2008 及更高版本，如其他答案中所示，还有更好的方法。
You can use INSERT with SELECT UNION ALL:
您可以将INSERT 与 SELECT UNION ALL 一起使用：
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
...
Only for small datasets though, which should be fine for your 4 records.
仅适用于小型数据集，这对于您的 4 条记录应该没问题。
回答by DavGarcia
INSERTstatements that use VALUESsyntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
INSERT使用VALUES语法的语句可以插入多行。为此，请包含多个列值列表，每个列表都用括号括起来并用逗号分隔。
Example:
例子：
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
