locked
Append data to a SELECT statement RRS feed

  • Question

  • I have to update CRM data with a data given in an Excel Spreadsheet. I have imported to the spreadsheet into a SQL table. Here is my select statement:
    SELECT cb.lastname, cb.emailaddress1, MAX(cb.ModifiedOn) AS LatestUpdated
    FROM crmdb.dbo.ContactBase cb
    WHERE EXISTS (SELECT p.firstname, p.lastname, p.emailaddress1
     FROM CRMTest.dbo.foo p
     WHERE p.firstname = cb.FirstName COLLATE SQL_Latin1_General_CP1_CI_AS AND
       p.lastname = cb.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AND
       p.emailaddress1 = cb.EMailAddress1 COLLATE SQL_Latin1_General_CP1_CI_AS)
     AND cb.ModifiedOn <= '09/30/2010'
    GROUP BY cb.lastname, cb.emailaddress1
    ORDER BY cb.EMailAddress1, cb.LastName
    The problem being is that the excel does not have the GUID that the CRM does, but I would like to append it to the SELECT statement. How can I do it, knowing that the database has a bunch of duplicates. For instances, one user may have registered multiple times with the same email address, or the wife of the user registered using the same email address as her husband. Finally, multiple people used one email ... In order to attempt to clean it up, the logic is to compare the firstname, lastname and email address, which eliminates the last two issues. But I still have to select one out of the ones that registered multiple times. This is why I decided to use the latest update as a criteria.
    So the statement gives me the right information however, I cannot add the ContactID with an INNER JOIN because it adds more data. HELP ????
    Tuesday, October 11, 2011 6:34 PM

Answers

  • HI Anooka !

    If you are using MS QL 2005 or above, lets try this out;

    ;WITH CTE AS(
    SELECT * , ROW_NUMBER() OVER(PARTITION BY cb.lastname, cb.emailaddress1 ORDER BY cb.ModifiedOn DESC) AS LatestUpdated
    FROM crmdb.dbo.ContactBase cb
    WHERE EXISTS (SELECT p.firstname, p.lastname, p.emailaddress1
        FROM CRMTest.dbo.foo p
        WHERE p.firstname = cb.FirstName COLLATE SQL_Latin1_General_CP1_CI_AS AND
           p.lastname = cb.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AND
           p.emailaddress1 = cb.EMailAddress1 COLLATE SQL_Latin1_General_CP1_CI_AS)
    AND cb.ModifiedOn <= '09/30/2010')
    SELECT * FROM CTE WHERE LatestUpdated = 1
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    • Marked as answer by Anooka Wednesday, October 12, 2011 1:54 PM
    Wednesday, October 12, 2011 1:30 PM
    Answerer

All replies

  • Is this for Microsoft Dynamics CRM? If so, you should be using the import capabilities within CRM to get the data updated and not SQL queries. In addition, you can import data using the CRM Web Services methods if you need something a bit more elaborate.


    MG.-
    Mariano Gomez, MIS, MCITP, PMP
    IntellPartners, LLC.
    http://www.intellpartners.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Tuesday, October 11, 2011 6:53 PM
  • Yes Mariano,

     

    This is Microsoft Dynamics CRM. I am not sure we have import capabilities within CRM 4.0. Are you thinking about something in particular? 


    Sincerely,

    Anooka
    Tuesday, October 11, 2011 6:56 PM
  • Are you just trying to generate the GUID?  Or you want the GUID from your database?  If trying to generate one using T-SQL; you can use NEWID() function, it will give you an non-sequential GUID.  Or you get use NEWSEQUENTIALID() to get sequntial list of GUIDs.

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://www.opsvault.com http://sqllearnings.blogspot.com/
    Tuesday, October 11, 2011 6:58 PM
  • No Mohit,

     

    I am trying to add the GUID already in the CRM database.


    Sincerely,

    Anooka
    Tuesday, October 11, 2011 7:00 PM
  • Mario, I have looked into the tools give in CRM 4.0. Thank you for giving me a direction; however, even though I have a way to upload them quite easily, I still need to retrieve the correct GUID. The data is really bad, but I am expected to get this done soon. Can you help?
    Sincerely,

    Anooka
    Wednesday, October 12, 2011 12:44 PM
  • HI Anooka !

    You may use the below to get your desired output;

    USE AdventureWorks
    GO
    SELECT NEWID() , * FROM Person.Contact
    
    
    --You t-SQL will look somethign like this
    SELECT NEWID(), cb.lastname, cb.emailaddress1, MAX(cb.ModifiedOn) AS LatestUpdated
    FROM crmdb.dbo.ContactBase cb
    WHERE EXISTS (SELECT p.firstname, p.lastname, p.emailaddress1
        FROM CRMTest.dbo.foo p
        WHERE p.firstname = cb.FirstName COLLATE SQL_Latin1_General_CP1_CI_AS AND
         p.lastname = cb.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AND
         p.emailaddress1 = cb.EMailAddress1 COLLATE SQL_Latin1_General_CP1_CI_AS)
    AND cb.ModifiedOn <= '09/30/2010'
    GROUP BY NEWID(), cb.lastname, cb.emailaddress1
    ORDER BY NEWID(), cb.EMailAddress1, cb.LastName
    
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Wednesday, October 12, 2011 12:52 PM
    Answerer
  • Hasham, thank you for replying; however, it is not what I am looking for.

     

    I am not trying to get create new ID's as they are already in the CRM database. I am trying to "attach" the ContactID which is already in place to the SELECT statement that I wrote above. I tried with an inner join and it does not work. The ones I selected with the SELECT statement is the correct one, I just need to add one more column. You would think it is easy, but it is not. I cannot add SELECT ContactID, Lastname, ... because each ContactID are different (duhh) but also I would have to mention it in the GROUP BY clause ...

     

    I am stuck


    Sincerely,

    Anooka
    Wednesday, October 12, 2011 1:05 PM
  • HI Anooka !

    If you are using MS QL 2005 or above, lets try this out;

    ;WITH CTE AS(
    SELECT * , ROW_NUMBER() OVER(PARTITION BY cb.lastname, cb.emailaddress1 ORDER BY cb.ModifiedOn DESC) AS LatestUpdated
    FROM crmdb.dbo.ContactBase cb
    WHERE EXISTS (SELECT p.firstname, p.lastname, p.emailaddress1
        FROM CRMTest.dbo.foo p
        WHERE p.firstname = cb.FirstName COLLATE SQL_Latin1_General_CP1_CI_AS AND
           p.lastname = cb.LastName COLLATE SQL_Latin1_General_CP1_CI_AS AND
           p.emailaddress1 = cb.EMailAddress1 COLLATE SQL_Latin1_General_CP1_CI_AS)
    AND cb.ModifiedOn <= '09/30/2010')
    SELECT * FROM CTE WHERE LatestUpdated = 1
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    • Marked as answer by Anooka Wednesday, October 12, 2011 1:54 PM
    Wednesday, October 12, 2011 1:30 PM
    Answerer
  • Hasham, 

     

    You are awesome!!!!!!!!!!!!!!!!!!!!!!!! Wow, thank you so much! It worked like a charm!


    Sincerely,

    Anooka
    Wednesday, October 12, 2011 1:55 PM
  • Anooka,

    I am not sure why you would need the GUID. In all the time I have been working with CRM uploading data, there hasn't been a need to deal with GUIDs. The import process will match the key on each entity (Customer Number, Item Number, Opportunity, etc.) and effect the changes needed or will create the corresponding row in the database and assign a GUID to it if it doesn't exist.


    MG.-
    Mariano Gomez, MIS, MCITP, PMP
    IntellPartners, LLC.
    http://www.intellpartners.com/
    Blog http://dynamicsgpblogster.blogspot.com/
    Thursday, October 13, 2011 11:20 AM