Moving Data from one table to another

[Bigbadborris]

Hi all

I have an access database which has 2 tables (IntContactsKnown & IntContactsUnKnown). Each of these has a field called KnownUnknown. When the Contact becomes Known a 1 is put in this field. I would like the program to read the data in IntContactsUnKnown and copy all the rows which have a 1 in the KnownUnknown field and paste them into the table IntContactsKnown. Then it should delete those records from IntContactsUnKnown.

Any help would be most appreciated

Many Thanks in advance

 

[jmcilhinney]

Do both those tables have the same schema? If so then that's poor design. If you have control of the database then you should merge those two tables into one. Simply have a column named Known or IsKnown and then 1 or True means known and 0 or False means unknown.

If you do want to stick with that seemingly dodgy arrangement then you will need to execute two SQL statements: one to copy the data and one to delete. E.g.

INSERT INTO DestinationTable (Column1, Column2)
SELECT Column1, Column2 FROM SourceTable WHERE SomeColumn = @SomeValue

That will copy the data and then the DELETE statement will be like any other DELETE statement, using the same WHERE clause. You'll want to use two separate OleDbCommand objects and call ExecuteNonQuery on each. I'd recommend using an OleDbTransaction to ensure that both commands execute successfully or neither do. You don't want to end up in a situation where half the job gets done.