Post reply

different table content

  • November 23, 2016 at 4:58 pm

    #316190

    Hi all ,

    I have to compare all tables in 2 databases . these 2 databases have the same table structure but different content

    If the content in table A in database A is different from the content in table A in database B then I want to insert the different into table A in database B

    How do I do this ?

    Many thanks

  • November 23, 2016 at 5:10 pm

    #1914351

    Maybe a ForEach table loop and a LEFT OUTER JOIN?

  • November 23, 2016 at 5:12 pm

    #1914352

    This is a hard to write set of code for a database. For a table, it's easier. You will want to look at EXCEPT to handle here, but really you are trying to compare every value in every row and determine if there are differences. Unless you have some PK and a way to compare rows and determine if they're different, this might not really be possible.

    There are tools, like Data Compare[/url], that can do this in general because searching and syncing data at any scale is cumbersome.

  • November 24, 2016 at 12:07 am

    #1914373

    WhiteLotus (11/23/2016)

    Hi all ,

    I have to compare all tables in 2 databases . these 2 databases have the same table structure but different content

    If the content in table A in database A is different from the content in table A in database B then I want to insert the different into table A in database B

    How do I do this ?

    Many thanks

    I suggest using MERGE

    --Synchronize the target table with

    --refreshed data from source table

    MERGE Products AS TARGET

    USING UpdatedProducts AS SOURCE

    ON (TARGET.ProductID = SOURCE.ProductID)

    --When records are matched, update

    --the records if there is any change

    WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName

    OR TARGET.Rate <> SOURCE.Rate THEN

    UPDATE SET TARGET.ProductName = SOURCE.ProductName,

    TARGET.Rate = SOURCE.Rate

    --When no records are matched, insert

    --the incoming records from source

    --table to target table

    WHEN NOT MATCHED BY TARGET THEN

    INSERT (ProductID, ProductName, Rate)

    VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)

    --When there is a row that exists in target table and

    --same record does not exist in source table

    --then delete this record from target table

    WHEN NOT MATCHED BY SOURCE THEN

    DELETE

    --$action specifies a column of type nvarchar(10)

    --in the OUTPUT clause that returns one of three

    --values for each row: 'INSERT', 'UPDATE', or 'DELETE',

    --according to the action that was performed on that row

    OUTPUT $action,

    DELETED.ProductID AS TargetProductID,

    DELETED.ProductName AS TargetProductName,

    DELETED.Rate AS TargetRate,

    INSERTED.ProductID AS SourceProductID,

    INSERTED.ProductName AS SourceProductName,

    INSERTED.Rate AS SourceRate;

  • November 29, 2016 at 9:19 pm

    #1915083

    thanks guys ..will try !

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply