delete a large amount of rows in a table

Question

delete a large amount of rows in a table

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

insanityflyff SSC Veteran Points: 250 More actions · Answer 1

well i wrote it like this right now

DELETE FROM [CHARACTER_01_DBF].[dbo].GUILD_MEMBER_TBL WHERE NOT EXISTS (SELECT (m_idPlayer) COLLATE DATABASE_DEFAULT FROM [LOGGING_01_DBF].[dbo].[LOG_LOGIN_TBL])

hopefully this will be better

and about the loop thing i dont know how to write it ...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

insanityflyff (2/27/2013)
well i wrote it like this right now
DELETE FROM [CHARACTER_01_DBF].[dbo].GUILD_MEMBER_TBL WHERE NOT EXISTS (SELECT (m_idPlayer) COLLATE DATABASE_DEFAULT FROM [LOGGING_01_DBF].[dbo].[LOG_LOGIN_TBL])
hopefully this will be better
and about the loop thing i dont know how to write it ...

Did you read the article I linked to in my previous post?

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

insanityflyff (2/27/2013)
and about the loop thing i dont know how to write it ...

DeleteMore:

waitfor delay 5 seconds (to give others some cpu)

delete top 10000 from tableA where ......

if @@rowcount > 0 goto DeleteMore

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Reading Lynn's article reminded me that sometimes I am concerned about t-log growth, so then I add a second counter to take a t-log backup every X deletes.