Take the 2-minute tour ×

This may seem strange at first but I hope it makes sense! I have a list<string> of mysql insert statements that I iterate through and execute on a one by one basis, this works ok but what I am trying to achieve is a performance boost.

I would like to pass all the values in a single query for example instead of

INSERT INTO tb1(field1,field2,field3) VALUES (1,2,3);
INSERT INTO tb1(field1,field2,field3) VALUES (4,5,6);
INSERT INTO tb1(field1,field2,field3) VALUES (7,8,9);

I would like to do

INSERT INTO tb1(field1,field2,field3) VALUES (1,2,3), (4,5,6), (7,8,9);

so basically I need to build a long query string keeping the first query as a starting point and appending the bracket section from values to the end for whatever the count of objects in the list may be - is this possible?

share|improve this question
Unless you want to do some text processing, I'd say, look at the source of the list<string> to have the values alone passed, then construct the sql. –  Srikanth Venugopalan Jan 24 '13 at 10:06
Yes you can! See here - stackoverflow.com/questions/452859/… –  Stephen Jan 24 '13 at 10:06
Can't you just concatenate all the individual inserts into a single query and execute it in one go, rather than executing each line separately? –  Rawling Jan 24 '13 at 10:24
@rawling yeah thats what im trying to achieve here because the statements im receiving are single statements i'd rather process them in bulk as i illustrated above –  JazziJeff Jan 25 '13 at 13:26
@stephen i already know that lol thats what i want to do! just need to convert single statements to this format but sorted anyway - see tims answer –  JazziJeff Jan 25 '13 at 13:27

2 Answers 2

up vote 2 down vote accepted

This should work. I'm using regex to find the columns and values and string.Substring to find the table-name. Then i put all together with string.Format and string.Join and a simple Linq query:

IEnumerable<string> inserts = // add your insert-strings here;
string firstInsert = inserts.First();
int tableIndex = firstInsert.IndexOf("INSERT INTO ") + "INSERT INTO ".Length;
string table = firstInsert.Substring(
    tableIndex, firstInsert.IndexOf("(", tableIndex) - tableIndex);
var regex = new System.Text.RegularExpressions.Regex(@"\(([^)]+)\)",System.Text.RegularExpressions.RegexOptions.Compiled);
string columns = regex.Matches(firstInsert)[0].Value;

IEnumerable<string> values = inserts.Select(sql => regex.Matches(sql)[1].Value);

string insertAll = string.Format("INSERT INTO {0}{1} VALUES {2};"
    , table
    , columns
    , string.Join(",", values));

DEMO with your sample data.

share|improve this answer
ive marked this as the answer, i have slightly modified the code to parse the statements and values on my end but this was more or less spot on so many thanks Tim :) –  JazziJeff Jan 25 '13 at 13:24

Just in case you might be inserting to different tables (or sets of columns within tables),

var perTableQueries = separateQueries
    .Select(s => s.Split(new[] {" VALUES "}, StringSplitOptions.None))
    .Where(a => a.Length == 2)
    .GroupBy(a => a[0], StringComparer.InvariantCultureIgnoreCase)
    .Select(g => String.Format("{0} VALUES {1};",
        String.Join(",", g.Select(a => a[1].TrimEnd(';')).ToArray())));

will make a single insert per table/column set (if your SQL is consistent).

That said, if you're making a separate request to the database for each line, your biggest performance saving might just be

var singleRequest = String.Join("", perTableQueries.ToArray());
share|improve this answer

Your Answer


