How To Copy DataRows Between DataTables by Using Visual Basic .NET

For a Microsoft Visual C# .NET version of this article, see
308909 .

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data
  • System.Data.SqlClient

IN THIS TASK

Summary

This article demonstrates how to copy specific DataRow objects from one table to another by using the ImportRow method of the DataTable class.

back to the top

How to Copy DataRows Between DataTables

Before you use the ImportRow method, you must ensure that the target table has the identical structure as the source table. This sample uses the Clone method of DataTable class to copy the structure of the DataTable, including all DataTable schemas, relations, and constraints.

This sample uses the Products table that is included with the Microsoft SQL Server Northwind database. The first five rows are copied from the Products table to another table that is created in memory.
  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

You can use the Copy method of a DataTable object to copy the entire DataTable:
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)
You can also copy DataRow objects from the results of a filtered DataView class or from the results of a Select method. For example:
'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

References

For additional information about how to manufacture an ADO recordset, click the following article number to view the article in the Microsoft Knowledge Base:
247868 How To Manufacture an ADO Recordset Based on Another Recordset
For more information on ADO.NET objects and syntax, refer to the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation: back to the top
Properties

Article ID: 305346 - Last Review: Mar 23, 2009 - Revision: 1

Feedback