Back to the top

How to Copy DataRows Between DataTables

1. Create a new Visual Basic .NET Console Application.
2. If the Code window is not open already, right-click Module1.vb in the Solution Explorer window, and click View Code.
3. Delete all of the code from the Code window.
4. Copy the following code and paste it in the Code window:

   Imports System.Data
   Imports System.Data.SqlClient
   Module Module1
   
       Sub Main()
           Dim ds As DataSet = New DataSet()
           Dim tblProducts As DataTable
           Dim tblProductsCopy As DataTable
   
           Dim tblProductsCount As Integer
           Dim tblProductsCopyCount As Integer
           Dim i As Integer
   
           'Change the connection string to your server.
           Dim conn As SqlConnection = New SqlConnection("Server=ServerName;database=Northwind;UID=<User ID>;PWD=<PassWord>")
           'Create the DataAdapter.
           Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from products", conn)
           'Fill the DataSet with data.
           da.Fill(ds, "products")
   
           
           tblProducts = ds.Tables("products")
           tblProductsCount = tblProducts.Rows.Count
           'Write the number of rows in Products table to the screen.
           Console.WriteLine("Table tblProducts has " & tblProductsCount.ToString & " Rows")
   
           'Loop through the top five rows and write the first column to the screen.
           For i = 0 To 4
               Console.WriteLine("Row(" & i.ToString & ") = " & tblProducts.Rows(i)(1))
           Next
   
           'The Clone method makes a copy of the table structure (Schema).
           tblProductsCopy = tblProducts.Clone
           'Use ImportRow method to copy from Products table to its clone.
           For i = 0 To 4
               tblProductsCopy.ImportRow(tblProducts.Rows(i))
           Next
   
           tblProductsCopyCount = tblProductsCopy.Rows.Count
           'Write blank line.
           Console.WriteLine()
           'Write the number of rows in tblProductsCopy table to the screen.
           Console.WriteLine("Table tblProductsCopy has " & tblProductsCopyCount.ToString & " Rows")
   
           'Loop through the top five rows and write the first column to the screen.
           For i = 0 To tblProductsCopyCount - 1
               Console.WriteLine("Row(" & i.ToString & ") = " & tblProductsCopy.Rows(i)(1))
           Next
   
           'This line keeps the console open until you press ENTER.
           Console.ReadLine()
   
       End Sub
   
   End Module
   					

5. Press the F5 key to build and run the project. Note that the program's output appears as follows in the Command window:

   Table tblProducts has 77 Rows
   Row(0) = Chai
   Row(1) = Chang
   Row(2) = Aniseed Syrup
   Row(3) = Chef Anton's Cajun Seasoning
   Row(4) = Chef Anton's Gumbo Mix
   
   Table tblProductsCopy has 5 Rows
   Row(0) = Chai
   Row(1) = Chang
   Row(2) = Aniseed Syrup
   Row(3) = Chef Anton's Cajun Seasoning
   Row(4) = Chef Anton's Gumbo Mix
   						

6. When you are finished, press ENTER to close the Command window.

Back to the top

Additional Notes

Dim MyDataRow As DataRow
Dim DataTable1 As New DataTable()
Dim DataTable2 As New DataTable()
Dim DataView1 As New DataView()
Dim DataSet1 as New DataSet()
' Copy the entire DataTable.
Dim dataTable2 = dataTable1.Copy()
dataSet1.Tables.Add(dataTable2)
				

'Copy from the results of a Select method.
For Each MydataRow In DataTable1.Select("Region = 'WA'")
  DataTable2.ImportRow(MyDataRow)
Next MyDataRow

'Copy from the results of a DataView.
DataView1 = DataTable1.DefaultView()
DataView1.RowFilter = "Region = 'WA'"
For I = 0 To Dataview1.Count - 1
  DataTable2.ImportRow(Dataview1.Item(I).Row)
Next I
				

Back to the top