How to insert a value starting with "@" in a MySQL table?

Question

I am trying to insert a large list of data in a MySQL table. Due to performance issues, I'am filling a List<string> and with this list I generate my command string to do an unique insert with all my rows.

Here is my code:

StringBuilder cmdText = new StringBuilder("INSERT INTO my_Table (ColumnA, ColumnB) VALUES ");

        List<string> aux = new List<string>();

        using (MySqlConnection conn = new MySqlConnection(connString))
        {
            foreach (DataRow row in dataTable.Rows)
            {
                aux.Add(string.Format("('{0}','{1}')", row[0].ToString(), row[1].ToSTring()));
            }

            cmdText.Append(string.Join(",", aux));
            cmdText.Append(";");
            conn.Open();
            using (MySqlCommand cmd = new MySqlCommand(cmdText.ToString(), conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 0;
                cmd.ExecuteNonQuery();
            };

This way, the result of my cmdText, finally is something like this:

INSERT INTO my_Table (ColumnA, ColumnB) VALUES ('Value1','Value2') ('Value3','Value4') ...;

with aproximately 500k rows in teh same insert.

One or more of this rows can contain values starting with '@'

I'm not using parameters ('@parameter'), but when I execute my application,

I get an error like this:

Fatal error encountered during command execution. "Parameter '@GMAIL.COM' must be defined."

@gmail.com is a value, not a parameter. But it still been interpreted like a @parameter.

Anyone knows the way to indicate that this is not a param but a value??

[Prepared statements.](http://stackoverflow.com/a/14376963/4802649) — Phiter, Dec 01 '16 at 10:01
Don't use string concatenation to build sql querys. **ALWAYS USE PARAMETRIZED QUERYS** — Pikoh, Dec 01 '16 at 10:03
if I dont use the concatenation it tooks me 3 hours to insert 500K rows — PeteMaikel, Dec 01 '16 at 10:06
Then you did something wrong. Parameterized queries are typically faster than plain ones when used in a loop. — Tomalak, Dec 01 '16 at 10:07
@Tomalak for 500K rows single insert even with parametarized will always be slow unless you go for bulk insert — Rahul, Dec 01 '16 at 10:08
where does your data come from? Why can't you just use `LOAD DATA INFILE` — e4c5, Dec 01 '16 at 10:14
show me how would you do a parameterized query with a 500k rows table in a foreach loop... I have tried this, opening the connection the foreach statement and doing ExecuteNonQuery inside the loop, and it tooks me 3 hours, with 500 Rows and 30 columns aproxitately — PeteMaikel, Dec 01 '16 at 10:21
@e4c5 the data comes from another database with UniVerse DB2 IBM engine — PeteMaikel, Dec 01 '16 at 10:25
so dump from that and import to mysql 500k should be doable in a couple of minutes — e4c5, Dec 01 '16 at 10:26

How to insert a value starting with "@" in a MySQL table?

0 Answers0