Delete In a certain Order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete In a certain Order

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-19 : 15:41:50

I have the following query which is archiving data off to another database. the query deletes 5000 rows each 2 seconds. the query first inserts into the archive tables and then deletes the rows in the current db. My problem is that i am getting a primary key violation error when inserting into the archive table. is it possible if i can insert and delete the data is the same order so not to get PK violation errors. Here is the query:

set rowcount 5000
Insert into ProTonga_Reed_archive..sign_ons
select *
from Sign_Ons
where K_Event_ID =@iEventid order by K_SO_ID asc

Delete from sign_ons where K_Event_ID = @iEventid order by K_SO_ID asc

while @@rowcount > 0
begin
WAITFOR DELAY '00:00:2'

Insert into ProTonga_Reed_archive..sign_ons
select *
from Sign_Ons
where K_Event_ID= @iEventid

Delete from sign_ons where K_Event_id =@iEventid
end

Can anybody help please!!
Authored by: anwarmir

Kristen
Test

22859 Posts

Posted - 2007-03-19 : 16:10:34

Generally the advised method is something like:


DELETE D
FROM MyTable AS D
     JOIN
(
    SELECT TOP 100 PERCENT MyPKCol1, MyPKCol2
    FROM MyTable
    ORDER BY MySortCol
) AS X
        ON X.MyPKCol1 = D.MyPKCol1
       AND X.MyPKCol2 = D.MyPKCol2

Kristen

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-19 : 18:39:34

Thanks Kirsten.
so if the I sort Insert by MysortCol and use the delete as suggested by you. the queries will affect the same rows. (Eventhough the set rowcount is set to 5000)?

I will give this a go tomorrow morning as i am out the office.

Thanks,

miranwar
Posting Yak Master

125 Posts

Posted - 2007-03-20 : 16:12:52

Thanks works perfectly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources