Copying a row from one table to another

I have a VB.NET 2003 WinForm application. In it, it must copy some rows from one table to another identical table in an Access database. Is there an SQL query that will do this?

For example, if the source table is called Table1, and the destination table is called Tabled2, and I want to copy the rows that have an ID greater than 10 from the one table to the other table, I would do something like this:

sSQL = "INSERT INTO Table2 (SELECT * FROM Table1 WHERE ID > 10)"

However, this isn't working. Any ideas? If it can't be done with an SQL query, then maybe an ADO.NET sample code?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ther was a good article written by Mike McIntyre

An ADO.NET question that comes up often in .NET forums is: "How do I select rows from a DataTable into
a new DataTable?", and the related question: "How can I select a subset of a DataTable into a new

The DataTable's Clone, Select, and ImportRow methods can be used to accomplish these tasks.

DataTable Clone Method
Clones the structure of a DataTable, including all DataTable schemas and constraints. See comment *1
in the example code.

DataTable Select Method
Gets an array of DataRow objects. See comment *2 in the example code.

DataTable ImportRow Method
Copies a DataRow into a DataTable, preserving any property settings, as well as original and current values.
See comment *3 in the example code.

Example Code
The example code below makes these assumptions:

A Visual Studio.NET VB.NET Windows Application project.
A DataSet containing a Customers DataTable. The schema for the DataTable is:
CustomerID (Integer)
CustomerName (String)
A DataGrid named OriginalCustomersDataGrid has been added to Form1 in the project.
A DataGrid named CustomersCopyDataGrid has been added to Form1 in the project.

Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
  Handles MyBase.Load
    ' The DemoDataSet used for this example contains a DataTable
    ' named Customers.  Make Customers the DataSource for
    ' the OriginalCustomersDataGrid.
    Me.OriginalCustomersDataGrid.DataSource = Me._DemoDataSet.Tables("Customers")
    ' Call this form's SelectIntoDataTableFunction
    ' passing it a SelectFilter expresssion and the Customers DataTable.
    ' Assign the result returned (new DataTable) as the DataSource for the
    ' CustomersCopyDataGrid.
    ' NOTE: In this example Customers with a CustomerID < 3 are being selected
    ' into a new DataTable.
    Me.CustomersCopyDataGrid.DataSource = SelectIntoDataTable("CustomerID < 3", _
End Sub

Private Function SelectIntoDataTable(ByVal selectFilter As String, _
  ByVal sourceDataTable As DataTable) As DataTable
    ' *1
    ' Declare a variable of type DataTable named newDataTable.
    ' Call the Clone method on the sourceDataTable passed
    ' into this function. This will produce an empty DataTable with the
    ' same structure as sourceDataTable. Assign the new DataTable
    ' to the newDataTable variable.
    Dim newDataTable As DataTable = sourceDataTable.Clone
    ' *2
    ' Declare a variable of type DataRow Array named dataRows.
    ' Call the Select method on the sourceDataTable passed
    ' into this function using the selectFilter passed into
    ' this function. Assign the DataRows returned to the
    ' dataRows variable.

    Dim dataRows As DataRow() = sourceDataTable.Select(selectFilter)
    ' Declare a variable of type DataRow named typeDataRow.
    Dim typeDataRow As DataRow
    ' Use the typeDataRow to loop through the rows of the sourceDataTable.
    For Each typeDataRow In dataRows
    ' *3
    ' Call newDataTable's ImportRow method
    ' to import a DataRow in sourceDataTable.
    ' Return the newDataTable.
    Return newDataTable
End Function

SETPAuthor Commented:
OK, figured out the problem - had a wrong table name :(

But now it's giving me a differnet error message - a concurrenmcy error. Because the first field in the table is an AutoNumber field, when I copy the new row it tries to put the same value for the AutoNumber field for both rows. ANy ways to fix this? I'd still like to use the "SELECT * FROM..." syntax instead of having to manually type out all the columns (i.e. SELECT Name, Surname, Address, ...") because there are literally over a hundred possible columns (depending which table the user selects)

SETPAuthor Commented:
Sorry guys - only saw your responses now... I don't think my briwser refreshed properly. You know what I'll do, I'll close off this question (split the points between the two of you) and start a new question

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.