Can anyone help me optimize this select into query...

[DrewMorris]

I have a table with approximately 2.5 million rows that contain multiple Text fields. I need to purge approximately 900,000 records from the table and I need to convert a few columns from text -> ntext.

I have attempted to use alter statements but these run too long on my test server (my maintenance window is 4 hours total) and I have attempted to use the select into statement below.

Select MessageID, UserID, FolderID, username, senddate, [from], replyto, sender, cc, bcc, [to], header, cast(folder as nvarchar) as folder, attachmentpaths, system, new, UnmarkNew,
[size], arrival, Path, Draft, SavedSend, ExpirationDate, IncludeInBrowse, flagged, repliedto, forwarded, isspam, CAST(Body AS ntext) as Body, CAST(attachments AS ntext) as attachments, CAST(attachmentfiles AS ntext) as attachmentfiles,
CAST(subject AS ntext) AS subject

INTO Table2
FROM Table1

WHERE MessageID NOT IN (select messageid from personal_mail where
(userid = 152997) OR (userid = 40628) OR (userid = 10202) OR (userid = 46177)
OR (userid = 119508) OR (userid = 92601) OR (userid = 76439) OR (userid = 115667)
OR (userid = 81743) OR (folderid = 5 AND arrival < '1/15/2005')
OR (folderid = 4 AND arrival < '1/15/2005') OR (folderid = 6 AND arrival < '1/15/2005')
)

Does anyone have any ideas for me?

Thanks in advance,

Drew

 

[dbomrrsm]

I would do a

Select * into table2 from table1

Then take your time doing what you suggest above ie your query but from table2 into table3 with all the wheres and casts etc then when thats finished and you have a minute window drop table1 and rename table3 to table1

[blue]DBomrrsm[/blue]