I have a situation where my program performs these two tasks

1. Fetch data via ODBC for 96 tables one after the other.
   a. Create insert commands sql file.
   b. Execute the inserts-script via SqlCmd command line utility.

I need to enhance this process so as I can utilize the full potential of the CPU via multi-threading

The ideal solution I am trying to implement is as such. The main thread will keep fetching the data via ODBC where as upon each table's data is fetched and stored in a file on disk the main thread then initiates a new Process of SqlCMD utility which inserts the data into SQL server and the main thread goes on to fetching the next table via ODBC.

The problem or the goal that I want to achieve is that I want to make sure that the new Process of SqlCmd start in a queue/sequence only when the prior processese of SqlCMD are finished

This method of exportOdbcToSQLDB is called in an outer loop 96 times.

private void exportOdbcToSQLDB(string tableName, SqlConnection sqlCon, OdbcConnection odbcCon)
 {
     InsertAndValidate insertValidateObj = new InsertAndValidate();
     insertValidateObj.sqlCon = sqlCon;
     insertValidateObj.odcConnection = odbcCon;
     insertValidateObj.logTableName = "_Log";

     if (string.IsNullOrEmpty(tableName))
         return;
     String columns = "";
     string tmpSelect = "SELECT * FROM [" + tableName + "]";
     using (var adapter = new SqlDataAdapter(tmpSelect, sqlCon))
     using (var builder = new SqlCommandBuilder(adapter))
     {
         columns = builder.GetInsertCommand().CommandText;
         columns = columns.Split(new String[] { ")" }, StringSplitOptions.None)[0];
         columns = columns.Split(new String[] { "(" }, StringSplitOptions.None)[1];
         columns = columns.Trim();
         Application.DoEvents();
     }

     int totalCount = 0;
     DateTime startTime = DateTime.Now;
     string strInsert = "";
     System.IO.File.WriteAllText("sql_" + tableName + ".sql", "");

     string tableFilePath = Path.Combine(Config.qbTablesDir, tableName + ".sql");
     using (StreamWriter writer = new StreamWriter(tableFilePath, true))
     {
         writer.WriteLine("SET NOCOUNT ON" + Helper1.Go());
         writer.Write("Update _Log set Remarks  = Concat(Remarks, '| Insert Start time ',dbo.dNow())" + Helper1.Go());
         string insertTemp = "Insert into [" + tableName + "] (" + columns + ") Values ";
         string queryString = @"select " + columns + " from [" + tableName + "]";

         insertValidateObj.addTableState(tableName);
         insertValidateObj.ResetStats(tableName);
         Application.DoEvents();

         OdbcCommand command = new OdbcCommand(queryString, insertValidateObj.odcConnection);
         using (OdbcDataReader reader = command.ExecuteReader())
         {
             int colCount = reader.FieldCount;
             if (reader.HasRows)
             {
                 try
                 {
                     while (reader.Read())
                     {
                         totalCount++;
                         writer.Write(insertTemp + this.GenerateInsert(reader, tableName, columns) + Helper1.Go());
                     }
                 }
                 catch (Exception ex)
                 {
                     string exception1 = (String.Concat("Row ", totalCount, ex.ToString()));
                 }
                 Application.DoEvents();
             }
             insertTemp = "Update _Log set Remarks  = Concat(Remarks, '| Insert End time ',dbo.dNow())" + Helper1.Go();
             insertTemp = "Update _log set SuccessRecords = (Select count(1) from [" + tableName + "]) where TableName = '" + tableName + "'" + Helper1.Go();
             writer.Write(insertTemp);
         }
     }
     DateTime endTime = DateTime.Now;
     insertValidateObj.SetStats(tableName, totalCount, 0, 0, startTime, endTime);

     if (totalCount > 0)
     {
         tablesToInsert.Enqueue(tableName);
         ExecuteQbInserts(tableName);
     }
 }

This method is called in the above method

public static void ExecuteQbInserts(string tableName)
 {
     string tmpPathInput = Path.Combine(Config.qbTablesDir, tableName + ".sql");
     string tmpPathOutput = Path.Combine(Config.qbSqlInsertsLogsDir, tableName + "_log.txt");

     string command = string.Concat("sqlcmd -S . -d qb", Config.ClientName, " -U sa -P atiqf -i \"", tmpPathInput, "\" -o \"", tmpPathOutput, "\"");
     ProcessStartInfo processInfo;
     processInfo = new ProcessStartInfo("cmd.exe", "/c " + command);
     processInfo.CreateNoWindow = false;
     processInfo.UseShellExecute = false;
     Process.Start(processInfo);
 }

Result:

Multiple SqlCMD processes runs when the rate of sqlCmd inserts is less than the rate at which the main thread fetches the ODBC data.

Solution required: Have the the main thread keeps getting data of 96 tables at its own rate and have sqlCMD processes run one after the other.