Using batch inserts and prepared statements

You can load data in batches using a prepared statement with parameters. You can also use transactions to rollback the batch load if any errors are encountered.

If you are loading large batches of data (more than 100MB), then consider using a direct batch insert.

The following example details using data contained in arrays, parameters, and a transaction to batch load data.

The test table used in the example is created with the command:

=> CREATE TABLE test (id INT, username VARCHAR(24), email VARCHAR(64), password VARCHAR(8));

For information about overlong strings in this scenario, see the AddEnforceLengthDuringCopyRewrite configuration parameter.

Example batch insert using parameters and transactions

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Vertica.Data.VerticaClient;
namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            VerticaConnectionStringBuilder builder = new VerticaConnectionStringBuilder();
            builder.Host = "192.168.1.10";
            builder.Database = "VMart";
            builder.User = "dbadmin";
            VerticaConnection _conn = new VerticaConnection(builder.ToString());
            _conn.Open();
            // Create arrays for column data
                    int[] ids = {1, 2, 3, 4};
            string[] usernames = {"user1", "user2", "user3", "user4"};
            string[] emails = { "user1@example.com", "user2@example.com","user3@example.com","user4@example.com" };
            string[] passwords = { "pass1", "pass2", "pass3", "pass4" };
            // create counters for accepted and rejected rows
                    int rows = 0;
            int rejRows = 0;
            bool error = false;
            // Create the transaction
                    VerticaTransaction txn = _conn.BeginTransaction();
            // Create the parameterized query and assign parameter types
                    VerticaCommand command = _conn.CreateCommand();
            command.CommandText = "insert into TEST values (@id, @username, @email, @password)";
            command.Parameters.Add(new VerticaParameter("id", VerticaType.BigInt));
            command.Parameters.Add(new VerticaParameter("username", VerticaType.VarChar));
            command.Parameters.Add(new VerticaParameter("email", VerticaType.VarChar));
            command.Parameters.Add(new VerticaParameter("password", VerticaType.VarChar));
            // Prepare the statement
                    command.Prepare();

                    // Loop through the column arrays and insert the data
                    for (int i = 0; i < ids.Length; i++)            {
                command.Parameters["id"].Value = ids[i];
                command.Parameters["username"].Value = usernames[i];
                command.Parameters["email"].Value = emails[i];
                command.Parameters["password"].Value = passwords[i];
                try
                {
                    rows += command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("\nInsert failed - \n  " + e.Message + "\n");
                    ++rejRows;
                    error = true;
                }
            }
            if (error)
            {
                // Roll back if errors
                        Console.WriteLine("Errors. Rolling Back Transaction.");
                Console.WriteLine(rejRows + " rows rejected.");
                txn.Rollback();
            }
            else
            {
                // Commit if no errors
                        Console.WriteLine("No Errors. Committing Transaction.");
                txn.Commit();
                Console.WriteLine("Inserted " + rows + " rows. ");
            }
            _conn.Close();
        }
    }
}