Copy the changed data into a table of same schema

Hi friends,
Can any body suggest how to copy the added rows from a source table to destination table on different servers of same structure.Thanks in advance

thanks satya...it has a column (PK) generating sequence no (6 digits)

If they are inserted in sequence you can insert rows that have PK greater than MAX(PK) on the destination table. Otherwise it would look something like


insert into dest_table
select *
from source_table a
where not exists
(
select NULL
from dest_table
where dest_table.pk = a.pk
)
Bambola.

Moved topic from Performance Tuning for Developers.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Do we have any performance difference if we use the below statement rather than using MAX(PK) for a table haveing 10 million rows and 100,000 rows as added daily to that table.
Thanks.

quote:Originally posted by bambola

If they are inserted in sequence you can insert rows that have PK greater than MAX(PK) on the destination table. Otherwise it would look something like


insert into dest_table
select *
from source_table a
where not exists
(
select NULL
from dest_table
where dest_table.pk = a.pk
)
Bambola.

Yes. Max(PK) will certainly perform better (assuming index on PK in both cases).

Bambola.

You could also use a trigger to do this upon insert into the first table...

or an outer join

insert into dest_table
select *
from source_table a
left outer join dest_table b
on b.pk = a.pk
where b.pk IS NULL


not sure if that is quicker or slower...