SQL bulk copy with mapped columns

Question

I am trying to bulk copy from one table to another by mapping the column names as the source and destination may not have same columns always.

Source can have 8 columns and destination can have 10 .. I need to map the columns and bulk copy.

Tried the below code..didn't work..getting Error: The given ColumnName 'moduleid' does not match up with any column in data source.

Source: existingtablecolumnsPresent has [collection time],[logtime],[moduleid],[node],[reason],[time],[timestamp],[usecaseid]

Destination: dataTable.Columns has [Node],[Time],[Reason],[Moduleid],[Usecaseid]

Please advise

 public static void BatchBulkCopy(DataTable dataTable, string DestinationTbl,  List<string> columnMapping,string filename)
    {
        var program = new Program();
        // Get the DataTable 
        DataTable dtInsertRows = dataTable;

        using (SqlBulkCopy sbc = new SqlBulkCopy(program.connectionStr.ToString()))
        {


            try { 
            sbc.DestinationTableName = DestinationTbl.ToLower();

            string sourceTableQuery = "Select top 1 * from " + "[" + dataTable.TableName + "]";
            DataTable dtSource = SqlHelper.ExecuteDataset(program.connectionStr.ToString(), CommandType.Text, sourceTableQuery).Tables[0];

            for (int i = 0; i < dataTable.Columns.Count; i++)
            {    //check if destination Column Exists in Source table
                if (dtSource.Columns.Cast<DataColumn>().Select(a => "[" + a.ColumnName.ToLower() + "]").Contains(dataTable.Columns[i].ToString().ToLower()))//contain method is not case sensitive
                {
                        List<string> existingtablecolumnsPresent = dtSource.Columns.Cast<DataColumn>().Select(a => "[" + a.ColumnName.ToLower() + "]").Distinct().OrderBy(t => t).ToList();

                        int sourceColumnIndex = existingtablecolumnsPresent.IndexOf(dataTable.Columns[i].ToString().ToLower());//Once column matched get its index
                    sbc.ColumnMappings.Add(dtSource.Columns[sourceColumnIndex].ToString(), dtSource.Columns[sourceColumnIndex].ToString());//give coluns name of source table rather then destination table so that it would avoid case sensitivity
                }

            }
            sbc.WriteToServer(dtInsertRows);
            sbc.Close();
        }

        catch (Exception ex)
        {
            Log.WriteLog("BatchBulkCopy" + " - " + filename, dataTable.TableName, ex.Message.ToString());

            // To move a file or folder to a new location:
            //if (File.Exists(program.sourceFile + filename))
            //    System.IO.File.Move(program.sourceFile + filename, program.failedfiles + filename);

        }

Answer 1

As requested (create a DataTable with the columns you want to insert in them- leave the others out. Make sure any columns you leave out are marked in the table for NULL or have a DEFAULT VALUE constraint (I can't show you how to do that unless you show me your table);

 //This first method is psuedoCode to explain how to create your datatable. You need to do it in the way that makes sense for you.
 public DataTable createDataTable(){
    List<string> excludedColumns = new List<string>();
    excludedColumns.Add("FieldToExclude");
    //...
    DataTable dt = new DataTable();
    foreach(string col in getColumns(myTable)){
         if(!excludedColumns.Contains(name)){
         DataColumn dC = new DataColumn(name,type);
         DataTable.Add(dC);
     }
     return dt;
}


 public List<string> getColumns(string tableName)
    {
        List<string> ret = new List<string>();
        using (SqlConnection conn = getConn())
        {
            conn.Open();
            using (SqlCommand com = conn.CreateCommand())
            {
                com.CommandText = "select column_Name from information_schema.COLUMNS where table_name = @tab";
                com.Parameters.AddWithValue("@tab", tableName);
                SqlDataReader read = com.ExecuteReader();
                While(read.Read()){
                ret.Add(Convert.ToString(read[0]);
            }
            conn.Close();
        }
        return ret;
    }
 //Now, you have a DataTable that has all the columns you want to insert. Map them yourself in code by adding to the appropriate column in your datatable.
 public bool isCopyInProgess = false;//not necessary - just part of my code
    public  void saveDataTable(string tableName, DataTable table)
    {
        using (SqlConnection conn = getConn())
        {
            conn.Open();
            using (var bulkCopy = new SqlBulkCopy(conn))//, SqlBulkCopyOptions.KeepIdentity))//un-comment if you want to use your own identity column
            {
                // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
                foreach (DataColumn col in table.Columns)
                {
                    //Console.WriteLine("mapping " + col.ColumnName+" ("+does_Column_Exist(col.ColumnName,"Item").ToString()+")");
                    bulkCopy.ColumnMappings.Add(col.ColumnName, "["+col.ColumnName+"]");
                   // Console.WriteLine("ok\n");
                }
                bulkCopy.BulkCopyTimeout = 8000;
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.BatchSize = 10000;
                bulkCopy.EnableStreaming = true;
                //bulkCopy.SqlRowsCopied += BulkCopy_SqlRowsCopied;
                //bulkCopy.NotifyAfter = 10000;
                isCopyInProgess = true;
               bulkCopy.WriteToServer(table);
            }
            conn.Close();
        }
    }

Also, use this as your bolumn checker:

 public bool does_Column_Exist(string colName,string tableName)
    {
        bool ret = false;
        using (SqlConnection conn = getConn())
        {
            conn.Open();
            using (SqlCommand com = conn.CreateCommand())
            {
                com.CommandText = "select count(*) from information_schema.COLUMNS where column_name = @col and table_name = @tab";
                com.Parameters.AddWithValue("@tab", tableName);
                com.Parameters.AddWithValue("@col", colName);
                ret = Convert.ToInt32(com.ExecuteScalar()) == 0 ? false : true; 
            }
            conn.Close();
        }
        return ret;
    }

Answer 2

Is there a specific reason you need C# for this? It seems like the path of least resistance would be to use SQL to do the job.

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;