none
adding all of the rows from one table to another RRS feed

  • Question

  • I have a database with 2 tables, the table called Template gets filtered through a Dataview and all of the resultng rows needs to be added to the other table in the database called Inspections. What is the best to add the filtered rows from the Template table to the inspections table. I tried merge but I lose rows .
    Monday, February 23, 2009 3:50 PM

Answers

  • Assuming destination and source tables have the same layout, is it possible to use ToTable on the View and then Merge it, like so:

            static void Main(string[] args)  
            {  
                DataTable dtSrc = new DataTable("Source");  
                dtSrc.Columns.Add("cid", typeof(int));  
                dtSrc.Columns.Add("ctxt", typeof(string));  
                dtSrc.Rows.Add(new object[] { 1, "Row 1" });  
                dtSrc.Rows.Add(new object[] { 2, "Row 2" });  
                dtSrc.Rows.Add(new object[] { 3, "Row 3" });  
                dtSrc.AcceptChanges();  
     
                DataTable dtDst = new DataTable("Destination");  
                dtDst.Columns.Add("cid", typeof(int));  
                dtDst.Columns.Add("ctxt", typeof(string));  
                dtDst.Rows.Add(new object[] { 1, "Row 1" });  
                dtDst.Rows.Add(new object[] { 2, "Row 2" });  
                dtDst.Rows.Add(new object[] { 3, "Row 3" });  
                dtDst.AcceptChanges();  
     
                PrintDataTable(dtSrc);  
                PrintDataTable(dtDst);  
     
                // Change row 1 & 2 in Source table  
                dtSrc.Rows[0][1] = "Edited 1";  
                dtSrc.Rows[1][1] = "Edited 2";  
     
                // Get filtered view  
                DataView view = new DataView(dtSrc);  
                view.RowStateFilter = DataViewRowState.ModifiedCurrent;  
                // Get table from view  
                DataTable dtView = view.ToTable("View");  
                PrintDataTable(dtView);  
                // Merge  
                dtDst.Merge(dtView, true);  
                PrintDataTable(dtDst);  
            }  
     
            public static void PrintDataTable(DataTable dt)  
            {  
                int colCount = dt.Columns.Count;  
                Console.WriteLine("\nTableName: {0}", dt.TableName);  
                foreach (DataRow r in dt.Rows)  
                {  
                    for (int i = 0; i < colCount; i++)  
                    {  
                        Console.Write("\t{0}", r[i].ToString());  
                    }  
                    Console.WriteLine();  
                }  
            } 

    Output:

    TableName: Source
            1       Row 1
            2       Row 2
            3       Row 3

    TableName: Destination
            1       Row 1
            2       Row 2
            3       Row 3

    TableName: View
            1       Edited 1
            2       Edited 2

    TableName: Destination
            1       Row 1
            2       Row 2
            3       Row 3
            1       Edited 1
            2       Edited 2

    // Michael



    -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 24, 2009 12:48 PM

All replies

  • Assuming destination and source tables have the same layout, is it possible to use ToTable on the View and then Merge it, like so:

            static void Main(string[] args)  
            {  
                DataTable dtSrc = new DataTable("Source");  
                dtSrc.Columns.Add("cid", typeof(int));  
                dtSrc.Columns.Add("ctxt", typeof(string));  
                dtSrc.Rows.Add(new object[] { 1, "Row 1" });  
                dtSrc.Rows.Add(new object[] { 2, "Row 2" });  
                dtSrc.Rows.Add(new object[] { 3, "Row 3" });  
                dtSrc.AcceptChanges();  
     
                DataTable dtDst = new DataTable("Destination");  
                dtDst.Columns.Add("cid", typeof(int));  
                dtDst.Columns.Add("ctxt", typeof(string));  
                dtDst.Rows.Add(new object[] { 1, "Row 1" });  
                dtDst.Rows.Add(new object[] { 2, "Row 2" });  
                dtDst.Rows.Add(new object[] { 3, "Row 3" });  
                dtDst.AcceptChanges();  
     
                PrintDataTable(dtSrc);  
                PrintDataTable(dtDst);  
     
                // Change row 1 & 2 in Source table  
                dtSrc.Rows[0][1] = "Edited 1";  
                dtSrc.Rows[1][1] = "Edited 2";  
     
                // Get filtered view  
                DataView view = new DataView(dtSrc);  
                view.RowStateFilter = DataViewRowState.ModifiedCurrent;  
                // Get table from view  
                DataTable dtView = view.ToTable("View");  
                PrintDataTable(dtView);  
                // Merge  
                dtDst.Merge(dtView, true);  
                PrintDataTable(dtDst);  
            }  
     
            public static void PrintDataTable(DataTable dt)  
            {  
                int colCount = dt.Columns.Count;  
                Console.WriteLine("\nTableName: {0}", dt.TableName);  
                foreach (DataRow r in dt.Rows)  
                {  
                    for (int i = 0; i < colCount; i++)  
                    {  
                        Console.Write("\t{0}", r[i].ToString());  
                    }  
                    Console.WriteLine();  
                }  
            } 

    Output:

    TableName: Source
            1       Row 1
            2       Row 2
            3       Row 3

    TableName: Destination
            1       Row 1
            2       Row 2
            3       Row 3

    TableName: View
            1       Edited 1
            2       Edited 2

    TableName: Destination
            1       Row 1
            2       Row 2
            3       Row 3
            1       Edited 1
            2       Edited 2

    // Michael



    -------------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 24, 2009 12:48 PM
  • Thanks, that worked. My problem seemed to be that both tables had an autonumber primary key that was messing up the merge.
    Wednesday, February 25, 2009 2:54 PM