There are instances that one needs to insert multiple records into a database. This is normally for those people having a group of items to be insterted.
Normal SQL statement does a single record insert like below
INSERT INTO mytable (Field1,Field2...) VALUES (value1, value2...)
This is good for a single record insert. What happens is that a person may select a group of items like shopping cart. In the shopping cart you have the item code, quantity and perhaps price as one record. But if user selects more than one item then it caused a database update issue. Usually the server side will use a grouping of the item in a list and do a loop to insert the records one by one.
The above method worked fine if you only have a small list of item to update. In the commercial world, the requirement can end up with thousands of items each with several fields to update. To do update using the above method most probably caused timeout problem on the server side. It has to loop thousands of insert statement within one submission.
The under mentioned method is not my own idea but is copied from the internet while searching for solutions. I would like to give credit to the person/s but has lost the information. Anyway, thanks to the availablilty of this information, it solved my problems.
The multiple record insert SQL statement runs like below
INSERT INTO mytable (Field1, Field2,...)
SELECT valuea1,valuea2,....
UNION ALL
SELECT valueb1,valueb2,...
UNION ALL
SELECT valuec1,valuec2,...
I have tested the SQL statement with MS SQL SERVER and it worked. Not sure if other database server can also work the same way.
This method does has its limitation though. Obviously string variables will ultimately has a length limit. Moreover, to parse huge lengths of strings may cause resource problems. After all, the manupulations are done in the server itself.
