Search code examples
sqlsql-serversql-server-2008

How can I insert 100000 rows in SQL Server?

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:

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Solution

  • 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.

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