November 5, 2012 at 10:16 am
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!
November 5, 2012 at 10:23 am
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/
November 5, 2012 at 10:46 am
Point taken - have updated my original post
November 5, 2012 at 10:55 am
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/
November 5, 2012 at 11:04 am
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!
November 5, 2012 at 11:16 am
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/
November 5, 2012 at 1:41 pm
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
November 5, 2012 at 1:46 pm
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 NameThank 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/
November 5, 2012 at 2:00 pm
Sorry - 1 level only
November 5, 2012 at 2:09 pm
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/
November 5, 2012 at 2:34 pm
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;
November 5, 2012 at 3:08 pm
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/
November 6, 2012 at 9:27 am
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!!!
November 6, 2012 at 9:37 am
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