How can I insert 100000 rows in SQL Server?

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


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