Best Way To Copy Data From One Table to another in the same database

Best Way To Copy Data From One Table to another in the same database

Post by Bruce Wallwi » Fri, 10 Nov 2000 04:00:00



Greetings,

I have several ideas of how I would like to do this, but I thought I would
throw this one out here for the masses to comment on.

I have a production database that has a table used for logging activity
information.  This table is a highly utilized table by a run-time
application.  My goal is to off load the data out of this table into a
offline table that can be used for reporting and such.

The goal I am trying to acheive is this:
1. I want this offline table to be in the same physical database as the
production.
2. I want an efficient way to select rows from the production table then
insert those rows into the offline table and delete the copied rows out of
the production table.

I am thinking that using DTS and creating a scheduled package is the best
way to accomplish this.

Has anyone had a similar problem to solve, if so how did you find it best
handled.

Thank you in advance for any comments or help.

Bruce Wallwin
Sr. Software Developer
Rockwell Automation

 
 
 

Best Way To Copy Data From One Table to another in the same database

Post by Wayne Snyde » Fri, 10 Nov 2000 04:00:00


You may use the
insert into historytable select * from origtable where .....
delete from origtable where .....

You can use schedulable DTS package to do the work. or
You can use a SQL Agent job to do the work...

I don't think it will really matter too much...

You might wish to consider the number of records which will be
deleted... If this is a large number, it may affect other users (due to
locking)... If this is true, you might wish to delete groups of records
in smaller batches....

Make sure you can re-start from any point and recover properly..



> Greetings,

> I have several ideas of how I would like to do this, but I thought I
would
> throw this one out here for the masses to comment on.

> I have a production database that has a table used for logging
activity
> information.  This table is a highly utilized table by a run-time
> application.  My goal is to off load the data out of this table into a
> offline table that can be used for reporting and such.

> The goal I am trying to acheive is this:
> 1. I want this offline table to be in the same physical database as
the
> production.
> 2. I want an efficient way to select rows from the production table
then
> insert those rows into the offline table and delete the copied rows
out of
> the production table.

> I am thinking that using DTS and creating a scheduled package is the
best
> way to accomplish this.

> Has anyone had a similar problem to solve, if so how did you find it
best
> handled.

> Thank you in advance for any comments or help.

> Bruce Wallwin
> Sr. Software Developer
> Rockwell Automation


--
Wayne Snyder MCSE, MCDBA, MCT, SQL SERVER MVP
IKON Education Services, C*te, NC
(Please respond only to the newsgroups.)

Sent via Deja.com http://www.veryComputer.com/
Before you buy.