How to DROP, CREATE Indexes in Another Database

[JohnBates]

hi experts,

I'm working on a simple stored proc to copy a tables' records to a table in another database.

Because there are 2 million rows to be copied, I want to drop the indexes in the destination database, perform the Insert, then create the indexes again.


In a stored procedure in the source database....
DROP INDEX Reporting.dbo.Claims.IX_Claims_1

get "Error 166: DROP INDEX does not allow specifying the database name as a prefix to the object name"

I can schedule a separate Job to drop the indexes just prior to the other Job that does the copy.... is there an easier way to do everything within 1 procedure ?

Thanks, John

 

[dbomrrsm]

use dts to do this and use a copy sql server objects task - this will copy over all the info you want quickly without having to drop indexes etc

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JohnBates]

Thanks DBomrrsm,

Yep, DTS would work but I need to select rows based on a date value. The date will "roll" from month to month as we only want to copy the most recent 13 months of data.
(The source table contains 6 million records and I don't have enough disk space to store the entire table in the Reporting database)

I'll probably just create 3 jobs and have the first job drop indexes, then execute the Copy Job which will execute the Job that creates indexes.

John