How to correlate data between two tables

  • Hey guys!

    Need your valuable help again, please! 😀

    I have various objects within one Salseforce system, which were migrated into another one, e.g. Account object.

    Using SQL 2008 I created two tables:

    (1) #oldAccount with columns Id, ParentId, Name, newId and newParentId

    (2) #newAccount with columns Id, ParentId and Name

    An only way to compare between those two objects is by their Name.

    I have wrote

    MERGE INTO #oldAccount

    USING (SELECT Id,

    ParentId,

    Name

    FROM #newAccount) n

    ON #oldAccount.Name = n.Name

    AND #oldAccount.ParentId = n.ParentId

    WHEN MATCHED THEN

    UPDATE

    SET newId = n.Id

    Although it doesn't update newParentId, the statement works as expected and updates newId for all the Accounts without ParentId.

    My problem starts when I have to update the following data and I need your suggestions, please:

    /*DROP TABLE #oldAccount;

    ALTER TABLE #oldAccount

    DROP CONSTRAINT oldAccount_PK

    DROP TABLE #newAccount

    ALTER TABLE #newAccount

    DROP CONSTRAINT newAccount_PK

    */

    CREATE TABLE #oldAccount (

    Id VARCHAR(18) NOT NULL CONSTRAINT oldAccount_PK PRIMARY KEY,

    ParentId VARCHAR(18) NOT NULL DEFAULT '',

    Name VARCHAR (40) NOT NULL,

    [newId] VARCHAR(18) NOT NULL DEFAULT '',

    newParentId VARCHAR(18) NOT NULL DEFAULT '');

    CREATE TABLE #newAccount (

    Id VARCHAR(18) NOT NULL CONSTRAINT newAccount_PK PRIMARY KEY,

    ParentId VARCHAR(18) NOT NULL DEFAULT '',

    Name VARCHAR (40) NOT NULL);

    WITH old_data AS (

    SELECT '00000XSAD' AS Id,

    '' AS ParentId,

    'MyAccount1' AS Name

    UNION ALL

    SELECT '00000XSAS',

    '00000XSAD',

    'MyAccount1'

    UNION ALL

    SELECT '00000XSAZ',

    '00000XSAD',

    'MyAccount1'

    UNION ALL

    SELECT '00000KIRE',

    '',

    'MyAccount2'

    UNION ALL

    SELECT '0000ADOTP',

    '',

    'MyAccount3')

    INSERT INTO #oldAccount(Id,ParentId,Name)

    SELECT * FROM old_data;

    WITH new_data AS (

    SELECT '0000QAWER' AS Id,

    '' AS ParentId,

    'MyAccount1' AS Name

    UNION ALL

    SELECT '0000QAOWQ',

    '0000QAWER',

    'MyAccount1'

    UNION ALL

    SELECT '0000WQ21A',

    '0000QAWER',

    'MyAccount1'

    UNION ALL

    SELECT '0000ZKI31',

    '',

    'MyAccount2'

    UNION ALL

    SELECT '0000ZIKA1',

    '',

    'MyAccount3')

    INSERT INTO #newAccount(Id,ParentId,Name)

    SELECT * FROM new_data;

    My assumption here is that the Ids are sequentional, i.e. an #oldAccount.myAccount1 with Id = 00000XSAS will become #newAccount.Id = 0000QAOWQ and #oldAccount.myAccount1 with Id = 00000XSAZ - will get #newAccount.Id = 0000WQ21A.

    Salesforce allows you to have more than one child account linked to a same Parent one and all

    of them may have a same Name, so any idea how to update newId and newParentId within oldAccount table will be MORE THAN APPRECIATED! 😀

    Many thanks in advance!

  • Your question and the details are very clear to you. Unfortunately we don't know what your tables or data looks like and we not familiar with your project. We need some more details to have much chance of helping. Please look at the first link in my signature about best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Point taken - have updated my original post

  • Zeev Kazhdan (11/5/2012)


    Point taken - have updated my original post

    Thanks for the ddl and sample data. I still have no idea what you are trying to do here.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I need to update newId and newParentId of #oldAccount with Id and ParentId of #newAccount respectively.

    At the moment I'm only able to update #oldAccount table with newId and only for the records without ParentId.

    I need forum's advise how to update #oldAccount, so:

    (1) newId for the records with ParentId will receive respective values from #newAccount;

    (2) ParentId will receive respective values from #newAccount;

    Thank you!

  • Zeev Kazhdan (11/5/2012)


    I need to update newId and newParentId of #oldAccount with Id and ParentId of #newAccount respectively.

    At the moment I'm only able to update #oldAccount table with newId and only for the records without ParentId.

    I need forum's advise how to update #oldAccount, so:

    (1) newId for the records with ParentId will receive respective values from #newAccount;

    (2) ParentId will receive respective values from #newAccount;

    Thank you!

    I am missing something. I understand that you want to update #oldAccount but where are the values coming from? What is the logic here? I think you are trying to update the column newId with the value of Id from #newAccount? Is Name the link between these two tables? This looks like it might be an adjacency list, can the level of parent be more than 1 or only 1 deep like your sample data?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • You're correct - the values for #oldAccount.newId and #oldAccount.newParentId will come from #newAccount.Id and #newAccount.ParentId respectively. An only common field between the two tables is Name

    Thank you

  • Zeev Kazhdan (11/5/2012)


    You're correct - the values for #oldAccount.newId and #oldAccount.newParentId will come from #newAccount.Id and #newAccount.ParentId respectively. An only common field between the two tables is Name

    Thank you

    But can the parent level be more than 1 deep? Or only 1 level deep like your sample shows?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sorry - 1 level only

  • Also there needs to be some rationale more than just name for when there are more than 1 child rows.

    Consider this:

    select * from #oldAccount where Name = 'MyAccount1' order by ParentId

    select * from #newAccount where Name = 'MyAccount1' order by ParentId

    Not it seems clear that OldAccount ID of "00000XSAD" will get the newID of "0000QAWER". That really isn't too hard to code. However, what rule is there for the children. Which row in #newAccount ties to "00000XSAS" and why?? There are 2 children in each table and nothing to say which one "links" to which one.

    Can you post what #oldAccount should look like after the update that you need help writing? Also, if you can answer my question about the logic here it would help a lot.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Please allow me try to explain Salesforce' logic, as I understand it:

    (1) each of the objects has an Id field, which is a string of 18 characters.

    (2) number of objects may also have ParentId (up to 1 level only)

    (3) my customer is going from an old Salesforce into the new Salesforce step by step. As a result, there is a gap between the

    data within the old Salesforce, which is still a live system, and the new one.

    (4) Their users can still modify the objects within the old system. The example I have used was the Account, but there are many other objects.

    (5) An only way to compare between the the records and update any change is by their unique field, e.g. Name in case of Account object, but the name can be the same for number of records.

    (6) Main objects, like account also have children objects, e.g. Contact and Contract. An only way to link Contact to Account is by Account.Id field: Account.Id = Contact.AccountId

    (7) As a thumb rule (couldn't get an answer from the customer) it seems that you export and import the data, based on its physical

    place within the file, so if I have MyAccount1 with Id's 10,20,30 in the old system it will keep the same order in the new system as well, but instead of 10, 20 and 30 it can be A, B and C (Id field is alphanumeric).

    So in my example, the outcome I would like to have is:

    UPDATE #oldAccount

    SET [newId] = CASE Id WHEN '00000XSAS' THEN '0000QAOWQ'

    WHEN '00000XSAZ' THEN '0000WQ21A'

    ELSE ''

    END,

    newParentId = CASE ParentId WHEN '00000XSAD' THEN '0000QAWER' ELSE '' END;

    SELECT * FROM #oldAccount;

  • OK I think this may be what you are looking for.

    ;with cte as

    (

    select *, ROW_NUMBER() over (PARTITION by Name order by ParentID) as RowNum

    from #oldAccount

    )

    ,cte2 as

    (

    select *, ROW_NUMBER() over (PARTITION by Name order by ParentID) as RowNum

    from #newAccount

    )

    update cte set newId = cte2.id, newParentId = cte2.ParentId

    from cte

    join cte2 on cte.Name = cte2.Name and cte.RowNum = cte2.RowNum

    select * from #oldAccount

    order by Name, Id, ParentId

    It at least works with the sample data and somewhat sketchy business rules you provided. Make sure you understand what this is doing before you put it on your production server.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Oh no !!!! :blush:

    It is pretty embarrassing! I didn't think about the update option at all it works like a charm with a minor change - ORDER BY ParentId, Id!

    Massive thanks and sorry for your time!!!

  • Zeev Kazhdan (11/6/2012)


    Oh no !!!! :blush:

    It is pretty embarrassing! I didn't think about the update option at all it works like a charm with a minor change - ORDER BY ParentId, Id!

    Massive thanks and sorry for your time!!!

    No need to be embarrassed!!! We all do that from time to time. Glad that I was able to help you get your problem sorted out. Thanks for letting me know.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 14 posts - 1 through 13 (of 13 total)

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