C# - Parse through DB2 column data and INSERT the data as new rows into SQL

WITH pquery (reqnum, subrow, status, date, userid, comment, status_string, date_string, userid_string, comment_string )
AS
(
--  Select the first token from the result strings
  SELECT reqnum, 1, 
    CASE WHEN LOCATE (',', status) > 0 THEN 
      SUBSTR (status, 1, LOCATE (',', status) - 1)
    ELSE
      status
    END status,
    CASE WHEN LOCATE (',', date) > 0 THEN 
      SUBSTR (date, 1, LOCATE (',', date) - 1)
    ELSE
      date
    END date,
    CASE WHEN LOCATE (',', userid) > 0 THEN 
      SUBSTR (userid, 1, LOCATE (',', userid) - 1)
    ELSE
      userid
    END userid,
    CASE WHEN LOCATE (',', comment) > 0 THEN 
      SUBSTR (comment, 1, LOCATE (',', comment) - 1)
    ELSE
      comment
    END comment,
    
--  Select the remainder strings that will be passed back to the query
    CASE WHEN locate (',', status) > 0 THEN
      LTRIM (SUBSTR (status, LOCATE (',', status) + 1))
    ELSE
      NULL
    END status_string,
    CASE WHEN locate (',', date) > 0 THEN
      LTRIM (SUBSTR (sentence, LOCATE (',', date) + 1))
    ELSE
      NULL
    END date_string,
    CASE WHEN locate (',', userid) > 0 THEN
      LTRIM (SUBSTR (userid, LOCATE (',', userid) + 1))
    ELSE
      NULL
    END userid_string,
    CASE WHEN locate (',', comment) > 0 THEN
      LTRIM (SUBSTR (comment, LOCATE (',', comment) + 1))
    ELSE
      NULL
    END comment_string
    
  FROM {yourtable} base 
  
  UNION ALL

--  Now select the first (next) token in each of the remainder strings
  SELECT reqnum, subrow + 1, 
    CASE WHEN LOCATE (',', status_string) > 0 then 
      SUBSTR (status_string, 1, LOCATE (',', status_string) - 1)
    ELSE
      status_string
    END status,
    CASE WHEN LOCATE (',', userid_string) > 0 then 
      SUBSTR (userid_string, 1, LOCATE (',', userid_string) - 1)
    ELSE
      userid_string
    END userid,
    CASE WHEN LOCATE (',', date_string) > 0 then 
      SUBSTR (date_string, 1, LOCATE (',', date_string) - 1)
    ELSE
      date_string
    END date,
    CASE WHEN LOCATE (',', comment_string) > 0 then 
      SUBSTR (comment_string, 1, LOCATE (',', comment_string) - 1)
    ELSE
      comment_string
    END comment,

--  And build the new remainder strings    
    CASE WHEN LOCATE (',', status_string) > 0 THEN
      LTRIM (SUBSTR (status_string, LOCATE (',', status_string) + 1))
    ELSE
      NULL
    END status_string
    CASE WHEN LOCATE (',', userid_string) > 0 THEN
      LTRIM (SUBSTR (userid_string, LOCATE (',', userid_string) + 1))
    ELSE
      NULL
    END userid_string
    CASE WHEN LOCATE (',', date_string) > 0 THEN
      LTRIM (SUBSTR (date_string, LOCATE (',', date_string) + 1))
    ELSE
      NULL
    END date_string
    CASE WHEN LOCATE (',', comment_string) > 0 THEN
      LTRIM (SUBSTR (comment_string, LOCATE (',', comment_string) + 1))
    ELSE
      NULL
    END comment_string
  FROM pquery t0
  WHERE t0.status_string IS NOT NULL
     OR t0.userid_string IS NOT NULL
     OR t0.date_string IS NOT NULL
     OR t0.comment_string IS NOT NULL
)
SELECT * 
FROM pquery pq;

Select allOpen in new window

/// <summary>
        /// getCXHistoryData is a method which makes a connection to the DB2 udb and returns a CXHistory Obj.
        /// </summary>
        /// <returns>cxhObjectsList is List of CXHistory Obj's</returns>
        public List<CXHISTORY> getCXHistoryData(){
            OleDbCommand oledbCMD;
            OleDbConnection oledbCONN;
            OleDbDataReader oledbReader;
            cxhObjectsList = new List<CXHISTORY>();
            errors = new List<string>();
            try
            {
                using (oledbCONN = new OleDbConnection(ApplicationConsts.DB2_CONNECTION_STRING))
                {
                    oledbCMD = new OleDbCommand(ApplicationConsts.HISTORY_QUERY, oledbCONN);
                    oledbCONN.Open();
                    using (oledbReader = oledbCMD.ExecuteReader())
                    {
                        while (oledbReader.Read())
                        {
                            int requestnum = ((int)oledbReader["REQUESTNUMBER"]);
                            string finstatus = oledbReader["FINALSTATUS"].ToString();
                            string dates = oledbReader["DATE"].ToString();
                            string userId = oledbReader["USERID"].ToString().ToUpper();
                            string status = oledbReader["STATUS"].ToString();
                            string comment = oledbReader["COMMENT"].ToString().Replace("@#@", ",");

                            string[] rowDates = dates.Split(',');
                            string[] rowUserId = userId.Split(',');
                            string[] rowStatus = status.Split(',');
                            string[] rowComment = comment.Split(',');

                            bool equal = rowDates.Length == rowUserId.Length && rowStatus.Length == rowComment.Length && rowDates.Length == rowStatus.Length;
                            if (!equal)
                            {
                                string error = string.Format("Error: REQUESTNUMBER: {0} Inconsistent number of entries found in row data.", requestnum);
                                errors.Add(error);
                                //throw new Exception(error);
                            }
                            else
                            {
                                for (int i = 0; i < rowDates.Length - 1; i++)
                                {
                                    CXHISTORY cxhObj = new CXHISTORY(requestnum, finstatus, rowDates[i], rowStatus[i], rowUserId[i], rowComment[i]);
                                    cxhObjectsList.Add(cxhObj);
                                    i++;
                                }
                            }
                        }
                    }
                }
            }
            catch(Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            return cxhObjectsList;
        }

Select allOpen in new window