Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012

    Unanswered: How to walk through all the columns of two different table

    I am two table. They are backup of each other. I would like to find out what the columns of a row that need to be sync.
    For example
    Record with ID = 2345, Update Name (From, to) (IreneLee, Irene)
    Record with ID = 2345, Update Birthday (From, to) (1975-04-24 , 1975-04-24 )
    Record with ID = 4568, Update Name (From, to) (VictorChu, Victor)

    I use this statement
    with cte1 As
    Select T1.ID As 'ID',
    T1.Name As 'T1Name',
    T2.Name As 'T2Name',
    T1.Birthday As 'T1Birthday' ,
    T2.Birthday As 'T2Birthday'
    from T1 inner join T2 on T1.ID = T2.ID
    Select * from cte1
    (eventually I have to programatically generate the above statement)
    Below is the result:
    ID T1.Name T2.Name T1.Birthday T2.Birthday
    1234 Richard Richard 1971-02-09 1971-02-09
    2345 IreneLee Irene 1975-04-24 1975-04-24
    3456 Danny Danny 1962-09-08 1962-09-08
    4568 VictorChu Victor 1962-09-08 1962-09-08

    And I use the below script
    DECLARE @tableName varchar(max)
    Set @tableName= 'T1'
    Select column_name From
    SELECT column_name
    where TABLE_NAME = @tableName
    SELECT column_name
    WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
    AND table_name = @tableName


    I need to find out what is different between each similar column (e.g. T1.Name and T2.Name) for each row It looks like I need two loop. I am not familiar in SQL script. Or maybe there is a better way to do that. Can anyone one suggest a way to do it. In other programming language, I would know how to structure this, but not in SQL. Any help will be much appreciate.

    This is what the logic I think I need. Please comment.
    For (row in Rows for JoinTable)
    For(column in Columns for ColumnTable)
    if ('T1'.column <> 'T2'.column)
    Insert ('T1.ID' , 'Update T2. ' + column name, 'T1'.column, 'T2'.column)
    into #ChangesTable

  2. #2
    Join Date
    Nov 2002
    >> I am two table. They are backup of each other. I would like to find out what the columns of a row that need to be sync.

    SELECT Col list
    FROM (
    SELECT Col list
    FROM Table 1
    SELECT Col list
    FROM Table2
    ) AS XXX
    GROUP BY Col list
    HAVING COUNT(*) = 1

    These are all the rows that need modification

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2012

    How do I get the list of changes more explicitily

    Thanks Brett,
    Based on your sql
    I get this
    ID Name Birthday
    2345 Irene 1976-04-24
    2345 IreneLee 1975-04-24
    4568 Victor 1962-09-08
    4568 VictorChu 1962-09-08

    Visually, I can see what changes. But I am hoping to get a list like

    2345 Update Name
    2345 Update Birthday
    4568 Update Name



    How can I do that?

Posting Permissions

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