Deleting newly added records with SP's / Triggers...

[jcg6]

I am using Stored Procedures and Triggers to move data from a "temp" table to the "real" table. Once the record is moved from the temp table to the real table, I want to be able to delete the original record in the temp table.

I have had success with this when I use the "delete from temptable" syntax, but that deletes all records in the "temp" table, and in this instance more than one record could possibly be entered at the same time.

To make a long story short...how can I ensure that I only delete the record that was just entered?

Any assistance would be appreciated. Thanks very much.

 

[SQLSister]

Depends on your data. Why are you moving records one at a time from the temp table? This is an inefficent way to do business. Are you using a cursor?

Only very rarely do I need to delete records from a temp table. If they are going to go someone, I move them in one set-based operation and then drop the temp table.

If you need to delete them use the same where clause that you used to identify the record to insert as part of your delete command.

Questions about posting. See faq183-874

 

[nigelrivett]

How are you picking the record to add? Use the same method to delete.
This is often done with an identity on the temp table. Pick a range of recs to process using the ID and use the ID t odelete that range at the end.
Do it all in a transaction and you have built in recovery.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ESquared]

Use a WHERE clause just like with a SELECT

DELETE Table WHERE Condition

 

[jcg6]

Thanks for your ideas.