如何在 SQL Server 中插入 100000 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7737472/
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
How can I insert 100000 rows in SQL Server?
提问by mekar10
INSERT INTO pantscolor_t (procode, color, pic)
VALUES
('74251', 'Black', '511black.jpg'),
('74251', 'OD Green', '511odgreen.jpg'),
('74251', 'Black', '511black.jpg'),
('74251', 'OD Green', '511odgreen.jpg'),
('74251', 'Black', '511black.jpg'),
('74251', 'OD Green', '511odgreen.jpg'),
..........
..........
..........
INSERT INTO pantscolor_t (procode,color,pic)
VALUES
('74251', 'Charcoal', '511charcoal.jpg'),
('74251', 'Charcoal', '511charcoal.jpg'),
('74251', 'Charcoal', '511charcoal.jpg'),
('74251', 'Charcoal', '511charcoal.jpg'),
.............
.............
.............
INSERT INTO........................
INSERT INTO........................
INSERT INTO........................
INSERT INTO........................
I have 100000 rows like this but my insert statements bigger than 1000 rows. When I run the SQL statement in SSMS, I get an error:
我有 100000 行这样但我的插入语句大于 1000 行。当我在 SSMS 中运行 SQL 语句时，出现错误：
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.
INSERT 语句中的行值表达式的数量超过了 1000 个行值的最大允许数量。
采纳答案by Bala
Create csv file (or some file with defined field delimiter and row delimiter) and use "BULK INSERT" option to load file to database. File can have 100000 rows; there won't be any problem of loading huge file using bulk upload.
创建 csv 文件（或一些具有定义字段分隔符和行分隔符的文件）并使用“BULK INSERT”选项将文件加载到数据库。文件可以有 100000 行；使用批量上传加载大文件不会有任何问题。
回答by Alex Jorgenson
Another solution is to use a select query with unions.
另一种解决方案是使用带有联合的选择查询。
INSERT INTO pantscolor_t (procode,color,pic)
SELECT '74251', 'Black', '511black.jpg'
UNION ALL SELECT '74251', 'OD Green', '511odgreen.jpg'
UNION ALL SELECT '74251', 'Black', '511black.jpg'
UNION ALL SELECT '74251', 'OD Green', '511odgreen.jpg'
UNION ALL SELECT '74251', 'Black', '511black.jpg'
UNION ALL SELECT '74251', 'OD Green', '511odgreen.jpg'
--etc....
UNION ALLis used instead of UNIONin order to speed up the query when dealing with thousands of records. UNION ALLallows for duplicate rows whereas UNIONwill ensure that duplicates do not exist in the result set. For this scenario we don't want to remove any possible duplicates, so UNION ALLis used.
UNION ALL用于UNION在处理数千条记录时，而不是为了加快查询速度。UNION ALL允许重复行，而UNION将确保结果集中不存在重复项。对于这种情况，我们不想删除任何可能的重复项，因此UNION ALL使用了它。
回答by Scorpion99
By applying the following you should not have any error :
通过应用以下内容，您应该不会有任何错误：
INSERT INTO pantscolor_t (procode,color,pic) VALUES ('74251','Black','511black.jpg')
INSERT INTO pantscolor_t (procode,color,pic) VALUES ('74251', 'OD Green', '511odgreen.jpg')
INSERT INTO pantscolor_t (procode,color,pic) VALUES ('74251', 'Black', '511black.jpg')
INSERT INTO pantscolor_t (procode,color,pic) VALUES ('74251', 'OD Green', '511odgreen.jpg')
INSERT INTO pantscolor_t (procode,color,pic) VALUES ('74251', 'Black', '511black.jpg')
...........
I tried it and it worked, of course you can use the excel to concatenate the values easily.
我试过了，它奏效了，当然您可以使用 excel 轻松连接值。
回答by user12408924
INSERT mytable (col1, col2, col3, col4, col5, col6)
SELECT * FROM (VALUES
('1502577', '0', '114', 'chodba', 'Praha', 'Praha 1'),
('1503483', '0', 'TVP', 'chodba', 'Praha', 'Praha 2'),
/* ... more than 1000 rows ... */
('1608107', '0', '8', 'sklad', 'Tluma?ov', 'Tluma?ov'),
('1608107', '0', '9', 'sklad', 'Tluma?ov', 'Tluma?ov')
) AS temp (col1, col2, col3, col4, col5, col6);
