How to make only one record at a time get inserted in the database table and also delete from the other table?

Once the details are filled ,I insert it in table1 as the new record and I delete the same record from table2.

Note:
Table2 is the table that is already containing the records.
Table 1 stores the records of table2 through textboxes and once this are stored then in table2 same record gets

deleted.

But as there are duplicate records here,the problem is when I try to insert the given details,all the duplicate records

also gets inserted and same in case of delete.

Is there any code that can make only one record to be inserted and deleted at a time?

I mean insert/delete only one record and not to affect other duplicate records.
I had written the insert delete command at code side in the button.
But,now due to duplicate records I don't know how to code for it.

When you insert a record, no other records are affected. A typical insert looks like

insert into [tablename] (list of field names) values(list of field values)

In this case, only one record is ever inserted. To delete a record the format is

delete from [tablename] where [sequence of fldname=value clauses]

When you execute the delete, all records matching the fldname=value restrictions are deleted. In order to avoid deleting multiple statements you must have some way of uniquely identifying a particular record. If you allow duplicate records then this is not possible. We need more information to be able to help. At the very least, to modify two tables you will need two queries. It would help if you posted the structure of the two tables, identifying primary keys (if defined).

You can use ROWCOUNT (http://msdn.microsoft.com/en-us/library/ms188774.aspx ) or TOP (http://msdn.microsoft.com/en-us/library/ms189463.aspx) to limit the number of records affected by your statements, if your data are EXACTLY the same in all fields.
I really don't like using TOP or ROWCOUNT, and try to create unique keys if possible.
Can you please explain what you are trying to do (why are you transfering data from table2 to table1)?
Can you alter table 2 and add a column?
Can you work in a temporary table for each set of duplicate data (or for the whole dataset to be transfered) ?

I almost forgot:
@Reverend Jim: There is also this type of insert that will affect more than 1 records:
insert into [tablename] (list of field names) select [list of fields] from [another table name]

Absolutely correct. I was assuming that the insert was being composed based on specific values being displayed in textboxes rather than a select clause on table2.

This article has been dead for over six months. Start a new discussion instead.