Extract only values from insert statement string?

Question

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?

Unless you want to do some text processing, I'd say, look at the source of the list to have the values alone passed, then construct the sql. — Srikanth Venugopalan, Jan 24 '13 at 10:06
Yes you can! See here - http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query — 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
What I mean is... if you're executing each line one by one, you can probably get a huge performance boost just by calling `String.Concat` on the lines and executing all of them at once, without even bothering to parse them into fewer `INSERT` statements. — Rawling, Jan 25 '13 at 13:29

Tim Schmelter · Accepted Answer · 2013-01-24T10:30:18.907

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.

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

score 1 · Answer 2 · answered Jan 24 '13 at 10:43

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};",
        g.Key,
        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());

Extract only values from insert statement string?

2 Answers2