Re: Need help with join/lookup
I am reading in 2 files - let me add the full script so maybe it makes more sense - was trying not to be too spammy. The net result for my T F is i end up making 3 CSV's that i run out through salesforce user update / add / deactivate processes.
Let me know if this makes it clearer
BEGIN CODE
******************************************
/* Set the schema to use */
USE sfdc;
/* Drop previous sfdc_users table if it still exists */
DROP TABLE IF EXISTS sfdc_users ;
/* Make new table */
CREATE TABLE sfdc_users (SFDC_Id TEXT(18) NULL COMMENT '');
ALTER TABLE sfdc_users CHANGE COLUMN `SFDC_Id` `SFDC_Id` MEDIUMTEXT NULL DEFAULT NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Username` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `LastName` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `FirstName` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `CompanyName` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Department` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Title` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Email` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Phone` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Fax` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `MobilePhone` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Alias` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `CommunityNickname` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `IsActive` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `TimeZoneSidKey` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `UserRoleId` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `LocaleSidKey` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ReceivesInfoEmails` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ReceivesAdminInfoEmails` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `EmailEncodingKey` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ProfileId` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `LanguageLocaleKey` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `EmployeeNumber` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `UserPermissionsMobileUser` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `UserPreferencesDisableAllFeedsEmail` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `UserPreferencesDisableFollowersEmail` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `UserPreferencesHideChatterOnboardingSplash` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `UserPreferencesHideSecondChatterOnboardingSplash` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `FederationIdentifier` TEXT(20) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `BMCServiceDesk__Broadcast_ticker_speed__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Other_Email_Address__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `NPI__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `eTenetID__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Job_Code__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Department_Code__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ADAM_Type__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ADAM_Company__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ADAM_Payroll__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ADAM_Work_Location__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `ADAM_OU_Location__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Role_Name__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `Profile_Name__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `UserLicenseType__c` TEXT(500) NULL COMMENT '';
ALTER TABLE sfdc_users ADD COLUMN `DOB__c` TEXT(500) NULL COMMENT '';
/* Load last SFDC_USER file */
LOAD DATA INFILE 'FAKEPATH' INTO TABLE sfdc_users CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
/* Add ID row so we can identify headers as row 1 */
ALTER TABLE sfdc_users ADD COLUMN `ID` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '' AFTER `UserLicenseType__c`;
/* Drop previous eidtable if it still exists */
DROP TABLE IF EXISTS eidtable_diff ;
/* Make new table */
CREATE TABLE eidtable_diff (`Unique_ID` VARCHAR(9) NULL COMMENT '');
ALTER TABLE eidtable_diff ADD COLUMN `NPI` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `First_Name` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Last_Name` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `eTenet_ID` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Title` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Job_Code` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Department_Code` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Department_Name` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Work_Phone` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Pager_Phone` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Fax` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Work_Email` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Alternate_Email` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Type` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Status` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Company` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Payroll_Location` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Work_Location` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `OU_Location` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Last_4` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `DOB` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Dell_Number` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Create_Date` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Terminate_Date` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `Account_Expiration` TEXT(500) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `User_Type` TEXT(500) NULL COMMENT '';
/* Load last DIFF file */
LOAD DATA INFILE 'FAKEPATH' INTO TABLE eidtable_diff CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
/* Add ID column after load so no errors */
ALTER TABLE eidtable_diff ADD COLUMN `IsInSFDC` TEXT(1) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `IsActiveInSFDC` TEXT(1) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `MakeInactive` TEXT(1) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `InsertInSFDC` TEXT(1) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `UpdateInSFDC` TEXT(1) NULL COMMENT '';
ALTER TABLE eidtable_diff ADD COLUMN `ID` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '' AFTER `UpdateInSFDC`;
/* Add headers in row 1 for T / F fields */
UPDATE eidtable_diff SET IsInSFDC = 'IsInSFDC' WHERE ID = 1;
UPDATE eidtable_diff SET IsActiveInSFDC = 'IsActiveInSFDC' WHERE ID = 1;
UPDATE eidtable_diff SET MakeInactive = 'MakeInactive' WHERE ID = 1;
UPDATE eidtable_diff SET InsertInSFDC = 'InsertInSFDC' WHERE ID = 1;
UPDATE eidtable_diff SET UpdateInSFDC = 'UpdateInSFDC' WHERE ID = 1;
/* Fix Wrong length ID's from EID */
UPDATE eidtable_diff SET Unique_ID = RIGHT (Unique_ID,9) WHERE ID > 1;
/* Fix Capitalization */
UPDATE eidtable_diff SET First_Name = CONCAT(UCASE(LEFT(First_Name, 1)),LCASE(SUBSTRING(First_Name, 2))) WHERE ID > 1;
UPDATE eidtable_diff SET Last_Name = CONCAT(UCASE(LEFT(Last_Name, 1)),LCASE(SUBSTRING(Last_Name, 2))) WHERE ID > 1;
UPDATE eidtable_diff SET eTenet_ID = LCASE(eTenet_ID) WHERE ID > 1;
UPDATE eidtable_diff SET Work_Email = LCASE(Work_Email) WHERE ID > 1;
UPDATE eidtable_diff SET Alternate_Email = LCASE(Alternate_Email) WHERE ID > 1;
UPDATE eidtable_diff SET DOB = CONCAT((LEFT(DOB,4)),'/',(LEFT(SUBSTRING(DOB,5),2)),'/',(LEFT(SUBSTRING(DOB,7),2))) WHERE ID > 1;
/* populate the T / F fields */
ALTER TABLE eidtable_diff ADD COLUMN `SFDC_Id_diff` TEXT(22) NULL COMMENT '';
UPDATE eidtable_diff SET IsInSFDC = 1 WHERE (ID > 1 AND EXISTS (SELECT * FROM sfdc_users WHERE `eidtable_diff`.`Unique_ID`=`sfdc_users`.`FederationIdentifier`));