If we want to insert multiple values into a table in SQL, we could use the following method:
INSERT INTO MyTable VALUES (1,'a','foo');
INSERT INTO MyTable VALUES (2,'b','bar');
INSERT INTO MyTable VALUES (3,'c','foo');
INSERT INTO MyTable VALUES (4,'d','bar');
INSERT INTO MyTable VALUES (5,'e','foo');
However, this is an RBAR operation which are generally seen as bad:
What is RBAR? How can I avoid it?
RBAR vs. Set based programming for SQL
So we would do a single set based INSERT statement:
INSERT INTO MyTable VALUES (1,'a','foo'), (2,'b','bar'), (3,'c','foo'), (4,'d','bar'), (5,'e','foo');
If we are creating a .NET application, and want to carry out the above insert on say a button click, we would use the ADO.NET libraries and would want to do the set based operation above rather than the RBAR one.
However, this doesn't seem easy to do and a Google search brought the following results
Parameterize insert of multiple rows
How should I multiple insert multiple records?
Inserting Multiple Records into SQL Server database using for loop
which seem to recommend executing an INSERT statement in a loop therefore running an INSERT per loop iteration (and therefore an RBAR operation)
I don't understand this, if RBAR is bad, why does there seem to be so little built in support for doing a set based operation in the ADO.NET libraries resulting in the best way of doing this being an RBAR?
