// sql create identity column table statement with a primary key
String sqlCreateTable = @"create set table idColTbl " +
    @"(Custid integer, IdCol integer generated always as identity, CustName varchar(30), " +
    @"State char(2)) unique primary index (Custid)";

// sql drop table statement
String sqlDropTable = "Drop table idColTbl";

// sql insert statement
String sqlInsert =
    @"Insert into idColTbl values (10,,'Customer1','OH');" +
    @"Insert into idColTbl values (20,,'Customer2','KY');";

// sql select statement
String sqlSelect = @"Select * from idColTbl";

// Teradata connection
TdConnection myConnection = new TdConnection ("Data Source= X; userid= Y; password= Z;");

try
{
   myConnection.Open();

   // create the table
   TdCommand myCommand = new TdCommand (sqlCreateTable, myConnection);
   myCommand.ExecuteNonQuery();

   // insert 2 rows
   TdCommand myInsertCmd = new TdCommand (sqlInsert, myConnection);
   myInsertCmd.ExecuteNonQuery();

   // create a TdDataAdapter & TdCommandBuilder
   // to generate the DataTable commands
   TdDataAdapter custDA = new TdDataAdapter (sqlSelect, myConnection);
   TdCommandBuilder custCB = new TdCommandBuilder (custDA);

   // create a cloned disconnected command
   TdCommand saveCmd = (TdCommand)((ICloneable)custCB.GetInsertCommand()).Clone();

   // null the TdDataAdapter so that the commands are not regenerated
   // from a new InsertCommand
   custCB.DataAdapter = null;

   // set the InsertCommand and enable generated data retrieval
   custDA.InsertCommand = saveCmd;
   custDA.InsertCommand.GeneratedDataBehavior = GeneratedDataBehavior.IdentityColumn;
   // Set the UpdatedRowSource property since the default sets this to None
   custDA.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

   // create a new dataset
   DataSet custDS = new DataSet();

   // populate the DataSet from the select command
   custDA.Fill (custDS, "idColTbl");

   // Code to modify data in the DataSet here.
   DataRowCollection rows = custDS.Tables ["idColTbl"].Rows;
   DataColumnCollection columns = custDS.Tables ["idColTbl"].Columns;

   // we don't set the identity column here as it will be populated by
   // the generated data retrieval feature
   DataRow newRow = custDS.Tables ["idColTbl"].NewRow();
   newRow ["Custid"] = 30;
   newRow ["Custname"] = "NewCust1";
   newRow ["State"] = "CA";

   rows.Add (newRow);
   newRow = custDS.Tables ["idColTbl"].NewRow();
   newRow ["Custid"] = 40;
   newRow ["Custname"] = "NewCust2";
   newRow ["State"] = "CA";

   // add the rows to the DataTable
   rows.Add (newRow);

   // the new rows will be added to the database and the identity columns
   // will be returned by the database
   int rowsPacted = custDA.Update (custDS, "idColTbl");

   int i = 0;

   // print out the values to confirm the updated identity columns
   foreach (DataRow selRow in rows)
   {
      Console.Write ("Row {0}", i++);
      foreach (DataColumn selCols in columns)
      {
         Console.Write(" {0} = {1}", selCols.ColumnName, selRow [selCols]);
      }
      Console.WriteLine();
   }

}
catch (TdException e)
{
   Console.WriteLine ("Error {0} - {1}", e.ErrorCode, e.Message);

   throw;
}
finally 
{
   // drop the table
   TdCommand cmdDropTable = new TdCommand (sqlDropTable, myConnection);
   cmdDropTable.ExecuteNonQuery();

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