(using Windows 2000 Server/SQL2K Ent Ed SP3a)
I am looking to create a job with a number of steps inside it. I need
the job to run on a sunday afternoon when our OLTP system is at our
quietest in terms of user connections and throughput. The job purpose
is to copy data (24,000,000 rows) from one table to another on the same
database, via a DTS package. I then need to rename the old table,
removing the triggers and views on it, then rename the new table, and
add the relevent objects (including indexs and constraints) then add
the permissions on it.
These are the steps:
1) Set restricted user mode on database
2) Run the DTS package
3) Remove triggers and views on old table
4) Rename old table
5) Rename new table
6) Add indexs and constraints to new table
7) Add triggers/views to new table
8) Set permissions to new table
9) Set multi user mode on database
My question would be does the above seem logical, or would there be a
better way of performing this process? Do I need the DTS package, would
a INSERT INTO be better, or could I use a DTS package for the entire
process?
Thanks and rgds,
qhHi
INSERT..SELECT may be a better option.
You may want to try something like the following to avoid the rename steps:
Create Archive Table
Insert Into Archive Table
Delete Archived Data from Live Table
ReIndex Live Table
You may also be able to do this in smaller increments each night instead of
once a w
.John
"Scott" wrote:
> Hi,
> (using Windows 2000 Server/SQL2K Ent Ed SP3a)
> I am looking to create a job with a number of steps inside it. I need
> the job to run on a sunday afternoon when our OLTP system is at our
> quietest in terms of user connections and throughput. The job purpose
> is to copy data (24,000,000 rows) from one table to another on the same
> database, via a DTS package. I then need to rename the old table,
> removing the triggers and views on it, then rename the new table, and
> add the relevent objects (including indexs and constraints) then add
> the permissions on it.
> These are the steps:
> 1) Set restricted user mode on database
> 2) Run the DTS package
> 3) Remove triggers and views on old table
> 4) Rename old table
> 5) Rename new table
> 6) Add indexs and constraints to new table
> 7) Add triggers/views to new table
> 8) Set permissions to new table
> 9) Set multi user mode on database
>
> My question would be does the above seem logical, or would there be a
> better way of performing this process? Do I need the DTS package, would
> a INSERT INTO be better, or could I use a DTS package for the entire
> process?
> Thanks and rgds,
> qh
>
