asked on

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?

Thanks

ASKER CERTIFIED SOLUTION

DotNetLover_Baan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

iboutchkine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SETP

ASKER

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)

Thanks

SETP

ASKER

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

Thakns