My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :

- 1 : the record have been modified
- 2 : have transfered to Reporting database

So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.

Thanks

To determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.

Now, to delete these records from the Reporting version of MyTable:

delete from RD.MyTable where pkey in (the above subquery)

"Resant" <resant_v (AT) yahoo (DOT) com> wrote

I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.

Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.

"Resant" <resant_v (AT) yahoo (DOT) com> wrote