Menu
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.
436
The basic syntax for bulk importing data is: INSERT ... SELECT * FROM OPENROWSET(BULK...) When used in an INSERT statement, OPENROWSET(BULK...)
First query USE CustomerDB; IF OBJECT_ID('Customer', 'U') IS NOT NULL DROP TABLE Customer; CREATE TABLE Customer ( CustomerID int PRIMARY KEY IDENTITY, CustomerName nvarchar(16), ...about 130 more columns... ); INSERT INTO Customer VALUES ('FirstCustomerName', ...), ... 1500 more rows...
It takes about 3 mins to insert 1 million rows if I run it in the SQL server and take about 10 mins if I use C# program to connect from my desktop. The tableA has a clustered index with 2 columns. My target is to make the insert as fast as possible (My idea target is within 1 min).
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.
192
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
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
