如何向MSSQL插入100000行?

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 ı run sql at SSMS it gives me an error : "The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values."

像这样有100000行但插入语句大于1000行。地对地导弹ırun sql时它给了我一个错误:“行值表达式在INSERT语句的数量超过允许的最大数量的1000行值。”

3 个解决方案

#1

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文件(或定义了字段分隔符和行分隔符的文件)，并使用“批量插入”选项将文件加载到数据库。文件可以有100000行;使用批量上传将不会有任何加载大文件的问题。

http://msdn.microsoft.com/en-us/library/ms188365.aspx

#2

Another solution is to use a select query with unions.

另一种解决方案是使用带有联合的select查询。

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 ALL is used instead of UNION in order to speed up the query when dealing with thousands of records. UNION ALL allows for duplicate rows whereas UNION will ensure that duplicates do not exist in the result set. For this scenario we don't want to remove any possible duplicates, so UNION ALL is used.

为了在处理数千条记录时加快查询的速度，UNION ALL被使用而不是UNION。UNION ALL允许重复行，而UNION将确保结果集中不存在重复行。

#3

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轻松地将值连接起来。

#1