SQL Server Performance

Copy the changed data into a table of same schema

Discussion in 'General Developer Questions' started by vvkp, Aug 20, 2003.

  1. vvkp New Member

    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
  2. satya Moderator

    Does that table has any identity field?
    Or any other field that may give loop to find out the new records, posting table structure will help.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. vvkp New Member

    thanks satya...it has a column (PK) generating sequence no (6 digits)
  4. bambola New Member

    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.
  5. gaurav_bindlish New Member

    Moved topic from Performance Tuning for Developers.

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

    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.
  7. bambola New Member

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

    Bambola.
  8. Twan New Member

    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...

Share This Page