Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Question Unanswered: SQL statement to sync data between 2 distinct tables

    Greetings,

    I am currently developing a PHP-based intranet system for my company... and using PEAR:DB as well. However, I am having troubles trying to create a generic SQL statement to sync all my records between 2 different tables in separate databases.

    Here is what I need to synchronize:


    MCINFO_table in Database1 --> Main table
    ====================
    Date * SN * MchNum * MchType * Location * Active
    ---------------------------------------------------
    02/26/01 * 12345 * SA23 * ASMAB339 * SC70 * D
    04/08/02 * 12345 * SA19 * ASMAB339 * SC70 * D
    02/17/03 * 12345 * SA08 * ASMAB339 * SC70 * A
    02/26/01 * 67890 * SA50 * ASMAB339 * SC50 * D
    02/17/03 * 67890 * SA34 * ASMAB339 * SC50 * A



    ENTITY_table in Database2 --> Backup table
    ===================
    Entity * EntityType * SerialNumber * Location
    --------------------------------------------
    SA08 * ASMAB339 * 12345 * SC70
    SA34 * ASMAB339 * 67890 * SC50


    To sum it up, I have a 'SYNC' button in my php page... whereby, once the user clicks on it, the system will synchronize any UPDATED or NEW data within MCINFO_table with ENTITY_table. In other words, any updated or new records within MCINFO_table (the main table) must be in-sync with ENTITY_table (the backup table).

    I will designate MCINFO_table as "M" and ENTITY_table as "E"..... And since both tables have varying fieldnames, these are the field mappings:

    M.MchNum = E.Entity
    M.MchType = E.EntityType
    M.SN = E.SerialNumber
    M.Location = E.Location

    When the SYNC button is pressed, I want the system to check EVERY ROW for any updated or new data in MCINFO_table where

    M.MchNum = E.Entity
    &
    M.Active = A
    &
    M.Date = between today's date & 2 days ago timeframe

    If these conditions are met, then perform the synchronization process with ENTITY_table.

    I've been unable to come up with the correct SQL statements for this sync process... can somebody please help me?... thank you so much :)

  2. #2
    Join Date
    Dec 2002
    Posts
    104

    Re: SQL statement to sync data between 2 distinct tables

    Originally posted by nickcwj
    Greetings,

    I am currently developing a PHP-based intranet system for my company... and using PEARB as well. However, I am having troubles trying to create a generic SQL statement to sync all my records between 2 different tables in separate databases.

    Here is what I need to synchronize:


    MCINFO_table in Database1 --> Main table
    ====================
    Date * SN * MchNum * MchType * Location * Active
    ---------------------------------------------------
    02/26/01 * 12345 * SA23 * ASMAB339 * SC70 * D
    04/08/02 * 12345 * SA19 * ASMAB339 * SC70 * D
    02/17/03 * 12345 * SA08 * ASMAB339 * SC70 * A
    02/26/01 * 67890 * SA50 * ASMAB339 * SC50 * D
    02/17/03 * 67890 * SA34 * ASMAB339 * SC50 * A



    ENTITY_table in Database2 --> Backup table
    ===================
    Entity * EntityType * SerialNumber * Location
    --------------------------------------------
    SA08 * ASMAB339 * 12345 * SC70
    SA34 * ASMAB339 * 67890 * SC50


    To sum it up, I have a 'SYNC' button in my php page... whereby, once the user clicks on it, the system will synchronize any UPDATED or NEW data within MCINFO_table with ENTITY_table. In other words, any updated or new records within MCINFO_table (the main table) must be in-sync with ENTITY_table (the backup table).

    I will designate MCINFO_table as "M" and ENTITY_table as "E"..... And since both tables have varying fieldnames, these are the field mappings:

    M.MchNum = E.Entity
    M.MchType = E.EntityType
    M.SN = E.SerialNumber
    M.Location = E.Location

    When the SYNC button is pressed, I want the system to check EVERY ROW for any updated or new data in MCINFO_table where

    M.MchNum = E.Entity
    &
    M.Active = A
    &
    M.Date = between today's date & 2 days ago timeframe

    If these conditions are met, then perform the synchronization process with ENTITY_table.

    I've been unable to come up with the correct SQL statements for this sync process... can somebody please help me?... thank you so much


    Hi!

    a crude method.....there can be many better ways...
    DECLARE @current_date datetime,
    @prev_date datetime

    SELECT @current_date = getdate(),
    @prev_date = dateadd(dd,-2,@current_date)

    UPDATE ENTITY_table
    set EntityType = M.MchType,
    SerialNumber = M.SN,
    Location = M.Location
    FROM MCINFO_table M
    WHERE M.MchNum = E.Entity
    AND M.Active = A
    AND M.Date >= @prev_date
    AND M.Date <= @current_date


    INSERT INTO ENTITY_table ( Entity,EntityType,SerialNumber,Location)
    SELECT MchNum,MchType,SN,Location
    FROM MCINFO_table M
    WHERE M.MchNum NOT IN (SELECT Entity
    FROM ENTITY_table)

    Assuming that MchNum is unique
    Hope this will work for u

    --Pooja

  3. #3
    Join Date
    Oct 2003
    Posts
    20

    Talking

    Hi pooja,

    I couldn't thank you more than enough for assisting me... i'll give your code a go and will let you know if it works... thnx again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •