Hi everyone, I need recommendations about the best way to merge two
identical database tables.

We have two database servers setup as follows

One database is located outside a firewall, all information entered from
outside the company is stored in this database.
We then have the exact same application/ database located inside our
firewall and all internal information is stored in this database.

We want to run reports based on the values entered in both databases so in
order to do this I must merge the information stored in the database outside
the firewall with the database information stored inside the firewall.

Each record in both tables uses an IDENTITY as the primary key so that it is
possible for the same identity to exist in the table outside the firewall as
well as in the table inside the firewall.

Can anyone recommend the best way to successfully merge the two tables?
Currently, I am just using a DTS package to import the information from
outside the firewall and I just append this information to the table located
inside the firewall, it works well but I am open to any ideas
(Replication???)

We are using SQL Server 2000 on both boxes

Thanks in advance
Mark

OK So the PK values(if you are using the IDENTITY() as the PK, may or
may not be the same but if they are it is coincidental.

Replication does this most gracefully.

Short of that you will have to


1. Be able to identify the same row in each table
2. Identify attributes that if changed on both sides are to have the
values from TableA or B kept.
3. Ideally have a change tracker on each side so you can tell what
happened to what tables on which side of the wall. You would then use
this as a feed to DTS.


Not an easy thing to do and this is why merge replication is so good.




"John" <dont (AT) spam (DOT) me> wrote

What's the natural key? If the IDENTITY is the only unique field, you
can't really compare them.

Hi folks, thanks for your help

The only key in the both tables is an IDENTITY integer

Regards
Mark


"Gary" <gary_strader (AT) wcc (DOT) ml.com> wrote