problems with deleting records from a table

[adrian]

i used a stored procedure to insert a record into another table and after inserting, i delete the record from the current table without using IFs.

If i run the stored procedure and i stop it a few times to simulate a failed job the records will all be deleted but not all of them will be inserted into the other table.

Have been trying this for quite some time... If i use IF statements to compare the values before deleting not all the records will be inserted.

Anyone have any idea on this? thanks

[mukund]

Hi,
Since the code snippet is not given, I can only think of making the INSERT & DELETE as part of Transaction should help.

-Mukund.

[adrian]

Hi,

thanx i managed to get it done. i think

i will have to fine tune it now. I'm using transaction to get it done. But i may face some problems later.

But anyway thanx again

[adrian]

Is there some other way to solve this problem? As the transaction take a very long time to process.

[ArtAlm]

Could a trigger solve your problem? If you make a trigger on insert table that delete from the other table ?

But when you say transaction is slow, do you make each insert and delete as a transaction, or the whole collection of isnerts and deletes as one transaction ?

Arthur

[adrian]

Originally posted by ArtAlm
Could a trigger solve your problem? If you make a trigger on insert table that delete from the other table ?

But when you say transaction is slow, do you make each insert and delete as a transaction, or the whole collection of isnerts and deletes as one transaction ?

Arthur

I thought of a trigger but then i need to compare the values before i delete the record away from the other table. Is the something like a global variable or something like that?

I made the whole collection as one transaction. Problems will occur if i make each insert and delete as a single transaction.

As i have to use a while loop i can only get the desired results if then transaction starts before the while loop and ends after it.

Thanks,
Adrian

[shefalin]

Hi,

I have another solution, might work for you.

Let the procedure insert the records from say Table1 to Table 2.
After the Insert (While Loop is completed), you could join the two tables based on the Primary Keys and delete the records from Table1 that match Table2.

Hope this helps.

[adrian]

Originally posted by shefalin
Hi,

I have another solution, might work for you.

Let the procedure insert the records from say Table1 to Table 2.
After the Insert (While Loop is completed), you could join the two tables based on the Primary Keys and delete the records from Table1 that match Table2.

Hope this helps.

Hi,

The problem with the DB is that i cannot put in the primary keys. If i put in the primary keys the job cannot even run. The one who created it didn't put in the primary keys.

So now i have to make do with it... there will be errors if i try to insert the keys.

Thanx for the help will think bout this, maybe it will inspire me thanx again

[ArtAlm]

Is it possible to make a new field on the table ?

Then you can insert a counter to that field so that each record has a unique number. Either use the autocounter in SQLServer, or update the table and insert a counter with a store procedure.

I had an similar problem a last week, I could not add the auto counter but added a new field and made a cursor that looped trough all records and added 1,2,3,...,n to the field. Then I could use this field to identify the records.

Arthur

[adrian]

Actually the table has a index field which automatically increments when there is a record being added.
Anyway arthur thanx for your help

I think i must bear with the processing until i find a way to solve it. been at it for days but still couldn't get it right.

Thanx again,
Adrian

[ArtAlm]

Can't you make a foreign key in table2 based on your counter in table1? If this is possible then I can't see any problem in making a store procedure that moves records from table1 to table2. But perhaps I misunderstand something here.


Arthur

[adrian]

There is some problems and restrictions with the table design and i can't change it.
The problem lies with the inserting and deleting of records if the stored procedures run successfully then there will be no problems at all. But if the job fails then the problem occurs.

I tried using cursor for each table to compare the values before deleting but there is no improvements...

Thanx for the help its ok i think i will just stick with the transaction.