I'm facing an issue in my C# application where I need to insert bulk data into a MySQL table and retrieve the last inserted ID for each row. I have two approaches to handle this, and I’m unsure which one is the most efficient or if there's a better way to do it.

First Approach: Using MySqlDataReader with the RETURNING clause

string insertQuery = @"""
    INSERT INTO users (Name, Age) 
    VALUES (@name1, @age1), (@name2, @age2), (@name3, @age3)
    """;  // Returns all inserted IDs

using (MySqlCommand cmd = new MySqlCommand(insertQuery, conn))
{
    cmd.Parameters.AddWithValue("@name1", "Alice");
    cmd.Parameters.AddWithValue("@age1", 25);
    cmd.Parameters.AddWithValue("@name2", "Bob");
    cmd.Parameters.AddWithValue("@age2", 30);
    cmd.Parameters.AddWithValue("@name3", "Charlie");
    cmd.Parameters.AddWithValue("@age3", 35);

    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int insertedId = reader.GetInt32(0);
            Console.WriteLine("Inserted ID: " + insertedId);
        }
    }
}

Second Approach: Inserting the rows and then using a SELECT query to retrieve the data without causing inconsistencies

// Insert rows first
// Then using MySqlCommand.LastInsertedId
using (MySqlCommand cmd = new MySqlCommand(StrQuery, conn, transaction))
{
    cmd.CommandType = CommandType.Text;
    if (mysqlParameters != null)
    {
        cmd.Parameters.AddRange(mysqlParameters);
    }
    cmd.ExecuteNonQuery();
    lastInsertedId = Convert.ToInt32(cmd.LastInsertedId);
}

My Question: Which approach should I use to ensure optimal performance and avoid data inconsistency? I’m using MySQL 8.0.33, and my bulk inserts usually range from 10 to 100 records, sometimes more.

Is there a better method for retrieving the last inserted IDs for each row?

I expected both approaches to retrieve the last inserted IDs for each row accurately. For the first approach, I anticipated receiving the IDs right after the insert, and for the second approach, I expected the IDs to be retrieved without affecting data consistency.

What Actually Happened: Both approaches worked, but I’m unsure which one is more efficient or less prone to issues, especially with larger datasets (10–100+ records). I’m also curious if there is a better or more efficient way to handle this scenario.

I'm facing an issue in my C# application where I need to insert bulk data into a MySQL table and retrieve the last inserted ID for each row. I have two approaches to handle this, and I’m unsure which one is the most efficient or if there's a better way to do it.

First Approach: Using MySqlDataReader with the RETURNING clause

string insertQuery = @"""
    INSERT INTO users (Name, Age) 
    VALUES (@name1, @age1), (@name2, @age2), (@name3, @age3)
    """;  // Returns all inserted IDs

using (MySqlCommand cmd = new MySqlCommand(insertQuery, conn))
{
    cmd.Parameters.AddWithValue("@name1", "Alice");
    cmd.Parameters.AddWithValue("@age1", 25);
    cmd.Parameters.AddWithValue("@name2", "Bob");
    cmd.Parameters.AddWithValue("@age2", 30);
    cmd.Parameters.AddWithValue("@name3", "Charlie");
    cmd.Parameters.AddWithValue("@age3", 35);

    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int insertedId = reader.GetInt32(0);
            Console.WriteLine("Inserted ID: " + insertedId);
        }
    }
}

Second Approach: Inserting the rows and then using a SELECT query to retrieve the data without causing inconsistencies

// Insert rows first
// Then using MySqlCommand.LastInsertedId
using (MySqlCommand cmd = new MySqlCommand(StrQuery, conn, transaction))
{
    cmd.CommandType = CommandType.Text;
    if (mysqlParameters != null)
    {
        cmd.Parameters.AddRange(mysqlParameters);
    }
    cmd.ExecuteNonQuery();
    lastInsertedId = Convert.ToInt32(cmd.LastInsertedId);
}

My Question: Which approach should I use to ensure optimal performance and avoid data inconsistency? I’m using MySQL 8.0.33, and my bulk inserts usually range from 10 to 100 records, sometimes more.

Is there a better method for retrieving the last inserted IDs for each row?

I expected both approaches to retrieve the last inserted IDs for each row accurately. For the first approach, I anticipated receiving the IDs right after the insert, and for the second approach, I expected the IDs to be retrieved without affecting data consistency.

What Actually Happened: Both approaches worked, but I’m unsure which one is more efficient or less prone to issues, especially with larger datasets (10–100+ records). I’m also curious if there is a better or more efficient way to handle this scenario.

• c#
• mysql
• winforms

Share Improve this question Follow edited Jan 31 at 11:54 BaderQasem asked Jan 31 at 10:29 BaderQasemBaderQasem 111 bronze badge 9

• MySQL doesn't have RETURNING. Are you using MariaDB perhaps? In that case you already get the generated IDs for all rows. The result order isn't guaranteed so if you need to identify which ID was created for which row you'll have to return more fields to identify it, eg RETURNING id,Name – Panagiotis Kanavos Commented Jan 31 at 10:42
• The second attempt can easily result in inconsistencies though, as the rows can be modified by other connections between INSERT and SELECT. – Panagiotis Kanavos Commented Jan 31 at 10:43
• no, I'm using MySQL but I found this and I'm not sure it's working, Do you have another way to do it (insert bulk data and retrieve the last inserted ID for each row) ?? – BaderQasem Commented Jan 31 at 11:50
• @PanagiotisKanavos how likely it is that another process will modify the auto incrementing ids of just inserted rows? I know that there is a possibility, but that can't be too high. Furthermore, if another transaction does modify any of these ids and the select returns the modified id, then that's not a particularly bad outcome as the returned id can be used to reference the row. – Shadow Commented Jan 31 at 17:27
• Extremely high, which is why transactions and locking exist. In a real system with multiple clients or a web app there are always at least two users/request modifying stuff in busy tables. If you don't use transactions it's guaranteed you'll get incorrect IDs within minutes – Panagiotis Kanavos Commented Jan 31 at 19:18

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 0

First Approach: Using RETURNING with MySqlDataReader ✅ Pros: Efficient as it retrieves all inserted IDs in one query. Ensures atomicity—IDs are fetched immediately upon insert. ❌ Cons: MySQL does not support RETURNING for multi-row inserts before MySQL 8.0.35 (introduced in MySQL 8.0.35). If you're on MySQL 8.0.33, this approach will not work.

Second Approach: Using LastInsertedId ✅ Pros: Works on MySQL 8.0.33. Safe for transactions, as LAST_INSERT_ID() returns the first inserted ID of the batch. ❌ Cons: Only gives the first inserted ID in a batch, not all. Does not work for concurrent inserts, as you need to calculate all IDs manually (by assuming they are sequential). For example, if the first inserted ID is 100, you would assume the next ones are 101, 102, 103, but this is not reliable in multi-user environments.

Best Alternative Approach: Using a Temporary Table + Multi-Insert Since you are inserting 10-100 records at a time, the safest way is to:

Insert data into the table. Use LAST_INSERT_ID() and ROW_COUNT() to determine the inserted ID range. Retrieve the exact inserted IDs using a SELECT query.

Code Implementation:

string insertQuery = @"
    INSERT INTO users (Name, Age) VALUES 
    (@name1, @age1), (@name2, @age2), (@name3, @age3);
";
using (MySqlCommand cmd = new MySqlCommand(insertQuery, conn))
{
    cmd.Parameters.AddWithValue("@name1", "Alice");
    cmd.Parameters.AddWithValue("@age1", 25);
    cmd.Parameters.AddWithValue("@name2", "Bob");
    cmd.Parameters.AddWithValue("@age2", 30);
    cmd.Parameters.AddWithValue("@name3", "Charlie");
    cmd.Parameters.AddWithValue("@age3", 35);

    cmd.ExecuteNonQuery();

    // Get the first inserted ID and the number of inserted rows
    int firstInsertedId = Convert.ToInt32(cmd.LastInsertedId);
    int rowCount = cmd.ExecuteNonQuery(); // Number of inserted rows

    // Fetch all inserted IDs
    string selectQuery = $"SELECT id FROM users WHERE id >= {firstInsertedId} ORDER BY id";
    using (MySqlCommand selectCmd = new MySqlCommand(selectQuery, conn))
    using (MySqlDataReader reader = selectCmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine("Inserted ID: " + reader.GetInt32(0));
        }
    }
}

Why This is Better: Works on MySQL 8.0.33. Ensures all inserted IDs are fetched accurately. Prevents ID conflicts in multi-user environments.

Final Recommendation:

RETURNING + MySqlDataReader: Works in MySQL 8.0.33? ❌ No (Only MySQL 8.0.35+) Retrieves All Inserted IDs? ✅ Yes Efficient?✅ Fastest Best for Production?✅ If on 8.0.35

LastInsertedId: Works in MySQL 8.0.33? ✅ Yes Retrieves All Inserted IDs? ❌ No (Only first ID) Efficient? ✅ Fast Best for Production? ❌ Risky for bulk inserts

Temp Table + SELECT after Insert: Works in MySQL 8.0.33? ✅ Yes Retrieves All Inserted IDs? ✅ Yes Efficient?✅ Fast Best for Production?✅ Recommended

*Since MySQL 8.0.33 does not support RETURNING, I recommend using the LastInsertedId + SELECT method.