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