// sql create CustomerHistory table statement
String sqlCTable = @"create table CustomerHistory (CustNo integer, " +
   "Record integer, Customer varchar(20))" +
   "unique primary index (CustNo, Record)";

// sql create Customer table statement
String sqlCTableNew = @"create table Customer (CustNo integer, " +
   "Record integer, Customer varchar(20), InsertTime timestamp(6) " +
   "default current_timestamp) unique primary index (CustNo, Record)";

// sql drop table statements
String sqlDropCustomer = "Drop table Customer";
String sqlDropHistory = "Drop table CustomerHistory";

// sql insert statement to insert select into the customer table
String sqlInsertNew =
   @"insert into Customer (CustNo, Record, Customer) select CustNo, " +
   @"Record, Customer from CustomerHistory where CustNo = 1";

// sql insert to load the customer history table
String sqlInsert =
   @"Insert into CustomerHistory values (1,1,'Customer1');" +
   @"Insert into CustomerHistory values (1,2,'Customer1');" +
   @"Insert into CustomerHistory values (1,3,'Customer1');" +
   @"Insert into CustomerHistory values (1,4,'Customer1');" +
   @"Insert into CustomerHistory values (2,1,'Customer2');" +
   @"Insert into CustomerHistory values (2,2,'Customer2');" +
   @"Insert into CustomerHistory values (2,3,'Customer2');" +
   @"Insert into CustomerHistory values (2,4,'Customer2');";

TdConnection myConnection = new TdConnection ("Data Source=tdnetdp2; userid=cc2; password=cc2;");

try
{
    myConnection.Open();

    // create a new command to create the 1st table and execute
    TdCommand myCommand = new TdCommand (sqlCTable, myConnection);
    myCommand.ExecuteNonQuery();

    // create a new command to create the 2nd table and execute
    myCommand = new TdCommand (sqlCTableNew, myConnection);
    myCommand.ExecuteNonQuery();

    // create a new insert command to load the customer history table
    TdCommand myInsertCmd = new TdCommand (sqlInsert, myConnection);
    myInsertCmd.ExecuteNonQuery();

    // use an insert-select command to insert new rows into a customer table
    myInsertCmd = new TdCommand (sqlInsertNew, myConnection);

    // set the generated data behavior
    myInsertCmd.GeneratedDataBehavior = GeneratedDataBehavior.AllColumns;

    // create a data reader to read the results
    TdDataReader reader = myInsertCmd.ExecuteReader();
    Int32 currentRow = 1;

    // validate the results
    do
    {
      while (reader.Read())
      {
          for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
          {
               Console.WriteLine ("Field [{0,4}] [{1,10}] = {2}",
                   currentRow, reader.GetName (columnIndex),
               reader.GetValue (columnIndex));
          }
          Console.WriteLine();
          currentRow++;
       }
    } while (true == reader.NextResult());

    // close the reader
    reader.Close();
}
catch (TdException e)
{
    Console.WriteLine ("Error {0} - {1}", e.ErrorCode, e.Message);
    throw;
}  
finally
{
    // drop the CustomerHistory table
    TdCommand cmdDropTable = new TdCommand (sqlDropHistory, myConnection);
    cmdDropTable.ExecuteNonQuery();

    // drop the Customer table
    cmdDropTable =  new TdCommand (sqlDropCustomer, myConnection);
    cmdDropTable.ExecuteNonQuery();

    //close the connection
    myConnection.Close();
}