select * from tbl_IMPORT_ClientOperators where eai_batchId = and eai_lineNumber = 1 select imp.* from tbl_IMPORT_ClientOperators imp with (nolock) inner join tbl_FSPro_members mem with (nolock) on mem.ownerId = imp.ownerId and mem.fsl_tablecode = imp.ownerType and mem.fspro_userId = imp.fspro_userId where imp.eai_batchId = and imp.ownerId = and imp.ownerType = and imp.eai_lineNumber > 0 update tbl_IMPORT_ClientOperators set fseUpdateStatus = 'M', fseContactId = where ownerId = and ownertype = and eai_batchid = and eai_lineNumber = and eai_importId = select * from tbl_IMPORT_ClientOperators imp with (nolock) where imp.eai_batchId = and imp.ownerId = and imp.ownerType = and imp.eai_lineNumber > 0 and imp.fseUpdateStatus is null and imp.fseContactId is null and imp.company_id <> '' and imp.contact_email <> '' select m.salutation, m.fspro_userid, m.firstName, m.lastName, m.email, m.title, m.caJobFunction, m.phone, m.phoneExt, m.fax, m.cellPhone, m.EmailYesNo from tbl_FSPro_members m where m.ownerId = and m.fsl_tablecode = and m.orgId = and m.orgType = 'OPR' and m.email = update tbl_IMPORT_ClientOperators set fseUpdateStatus = 'M', fseContactId = where ownerId = and ownertype = and eai_batchid = and contact_email = and company_id = and fseUpdateStatus is null select * from tbl_IMPORT_ClientOperators imp with (nolock) where imp.eai_batchId = and imp.ownerId = and imp.ownerType = and imp.eai_lineNumber > 0 and imp.fseUpdateStatus is null and imp.fseContactId is null and imp.contact_email <> '' select mem.fspro_userid from tbl_FSPro_members mem with (nolock) where mem.ownerId = and mem.fsl_tablecode = and mem.email = update tbl_IMPORT_ClientOperators set fseUpdateStatus = 'M', fseContactId = where ownerId = and ownertype = and eai_batchid = and contact_email = and fseUpdateStatus is null select imp.eai_importId, imp.operatorId, imp.ownerId, imp.ownerType, imp.fspro_userId, imp.recordSource, imp.company_id, imp.company_name, imp.company_phone, imp.company_address1, imp.company_address2, imp.company_city, imp.company_state, imp.company_zip, imp.contact_last_name, imp.contact_first_name, imp.contact_mobile_phone, imp.contact_phone, imp.contact_email, imp.contact_type, imp.contact_title, trim(imp.email_unsubscribe) as email_unsubscribe, imp.contact_phone_ext, imp.contact_zip, imp.contact_state, imp.contact_city, imp.contact_address2, imp.contact_address1, imp.company_territory, imp.relationship_rank, imp.contact_function, imp.contact_linkedIn, imp.contact_preferred_communication, imp.contact_preferred_language, trim(imp.decision_maker) as decision_maker, imp.eai_batchId, imp.eai_lineNumber, imp.fseContactId, imp.fseUpdateStatus, trim(imp.primary_contact_indicator) as primary_contact_indicator from tbl_IMPORT_ClientOperators imp with (nolock) where imp.ownerId = and imp.ownerType = and imp.eai_batchId = and imp.fseUpdateStatus = 'M' and imp.fseContactId is not null select imp.eai_importId, imp.operatorId, imp.ownerId, imp.ownerType, imp.fspro_userId, imp.recordSource, imp.company_id, imp.company_name, imp.company_phone, imp.company_address1, imp.company_address2, imp.company_city, imp.company_state, imp.company_zip, imp.contact_last_name, imp.contact_first_name, imp.contact_mobile_phone, imp.contact_phone, imp.contact_email, imp.contact_type, imp.contact_title, trim(imp.email_unsubscribe) as email_unsubscribe, imp.contact_phone_ext, imp.contact_zip, imp.contact_state, imp.contact_city, imp.contact_address2, imp.contact_address1, imp.company_territory, imp.relationship_rank, imp.contact_function, imp.contact_linkedIn, imp.contact_preferred_communication, imp.contact_preferred_language, trim(imp.decision_maker) as decision_maker, imp.eai_batchId, imp.eai_lineNumber, imp.fseContactId, imp.fseUpdateStatus, trim(imp.primary_contact_indicator) as primary_contact_indicator from tbl_IMPORT_ClientOperators imp with (nolock) where imp.ownerId = and imp.ownerType = and imp.eai_batchId = and imp.fseUpdateStatus is null and imp.fseContactId is null select jobFunctionId from qmd_partnerContactJobFunctions where jobFunctionName = select rankId from qmd_partnerContactRelationshipRanks where rankName = set nocount on insert into tbl_FSPro_members ( firstName, lastName, title, email, phone, phoneExt, cellPhone, officeAddrSame, createDate, lastUpdated, originalSource, orgId, orgType, ownerId, fsl_tablecode, cajobfunction, caRelationshipRank, preferredCommunication, EmailYesNo, contactDecisionMaker, linkedIn, address1, address2, City, State, ZipCode, preferredLanguage ) values( , , , , , , , 1, , , , , , , , , , , , , , , , , , , ) select contactId = @@identity set nocount off update tbl_OPR_ClientOperators set primaryContactId = , lastUpdated = where operatorId = and ownerId = and fslTableCode = update tbl_OPR_ClientOperators set primaryContactId = , lastUpdated = where operatorId = and ownerId = and fslTableCode = and primaryContactId = update tbl_IMPORT_ClientOperators set fseUpdateStatus = , fseContactId = where eai_batchId = and eai_importId = and ownerId = and ownerType = select jobFunctionId from qmd_partnerContactJobFunctions where jobFunctionName = select rankId from qmd_partnerContactRelationshipRanks where rankName = update m set m.firstName = case when coalesce( m.firstName, '' ) = '' then i.contact_first_name else m.firstName end, m.lastName = case when coalesce( m.lastName, '' ) = '' then i.contact_last_name else m.lastName end, m.title = case when coalesce( m.title, '' ) = '' then i.contact_title else m.title end, m.email = case when coalesce( m.email, '' ) = '' then i.contact_email else m.email end, m.phone = case when coalesce( m.phone, '' ) = '' then i.contact_phone else m.phone end, m.phoneExt = case when coalesce( m.phoneExt, '' ) = '' then i.contact_phone_ext else m.phoneExt end, m.cellPhone = case when coalesce( m.cellPhone, '' ) = '' then i.contact_mobile_phone else m.cellPhone end, m.Address1 = case when coalesce( m.Address1, '' ) = '' then i.contact_address1 else m.Address1 end, m.Address2 = case when coalesce( m.Address2, '' ) = '' then i.contact_address2 else m.Address2 end, m.city = case when coalesce( m.city, '' ) = '' then i.contact_city else m.city end, m.state = case when coalesce( m.state, '' ) = '' then i.contact_state else m.state end, m.ZipCode = case when coalesce( m.ZipCode, '' ) = '' then i.contact_zip else m.ZipCode end, m.preferredLanguage = case when coalesce( m.preferredLanguage, '' ) = '' then lang.languageCode else m.preferredLanguage end, m.preferredCommunication = case when coalesce(m.preferredCommunication, '') = '' then '#_prefCommMethod#' else m.preferredCommunication end, m.cajobfunction = case when coalesce(m.cajobfunction, '') = '' then #val(qmd_lookupJobFunction.jobFunctionId)# else m.cajobfunction end, m.caRelationshipRank = case when coalesce(m.caRelationshipRank, '') = '' then #val(qmd_lookUpRelationshipRank.rankId)# else m.caRelationshipRank end, m.linkedIn = case when coalesce(m.linkedIn, '') = '' then i.contact_linkedIn else m.linkedIn end, m.EmailYesNo = case when coalesce(m.EmailYesNo, '') = '' then #_email_unsubscribe_bit# else m.EmailYesNo end, m.contactDecisionMaker = case when coalesce(m.contactDecisionMaker, '') = '' then '#decision_maker#' else m.contactDecisionMaker end, m.lastUpdated = , m.orgId = case when coalesce(m.orgId, '') = '' then i.company_id else m.orgId end, m.orgType = case when coalesce(m.orgType, '') = '' then 'OPR' else m.orgType end from tbl_IMPORT_ClientOperators i inner join tbl_FSPro_members m on m.fspro_userid = i.fseContactId left outer join tbl_CRM_Languages lang on lang.languageName = i.contact_preferred_language where i.eai_batchId = and i.eai_importId = and i.ownerId = and i.ownerType = update tbl_IMPORT_ClientOperators set fseUpdateStatus = 'U' where eai_batchId = and eai_importId = and fseUpdateStatus = 'M' and fseContactId = and ownerId = and ownerType = update m set m.firstName = , m.lastName = , m.title = , m.email = , m.phone = , m.phoneExt = , m.cellPhone = , m.Address1 = , m.Address2 = , m.city = , m.state = , m.ZipCode = , m.preferredLanguage = , m.preferredCommunication = , m.cajobfunction = , m.caRelationshipRank = , m.EmailYesNo = , m.contactDecisionMaker = , m.linkedIn = , m.orgId = , m.orgType = , m.lastUpdated = from tbl_FSPro_members m where m.ownerId = and m.fsl_tableCode = and m.FSPro_UserID = update tbl_OPR_ClientOperators set primaryContactId = , lastUpdated = where operatorId = and ownerId = and fslTableCode = update tbl_OPR_ClientOperators set primaryContactId = , lastUpdated = where operatorId = and ownerId = and fslTableCode = and primaryContactId = select contact_first_name, contact_last_name, contact_email, company_name, company_territory from tbl_IMPORT_ClientOperators with (nolock) where ownerId = and ownerType = and eai_batchId = and fseUpdateStatus = 'M' and fseContactId is not null select contact_first_name, contact_last_name, contact_email, company_name, company_territory from tbl_IMPORT_ClientOperators with (nolock) where ownerId = and ownerType = and eai_batchId = and fseUpdateStatus = 'A' and fseContactId is not null select contact_first_name, contact_last_name, contact_email, company_name, company_territory from tbl_IMPORT_ClientOperators with (nolock) where ownerId = and ownerType = and eai_batchId = and fseUpdateStatus = 'U' and fseContactId is not null select contact_first_name, contact_last_name, contact_email, company_name, company_territory from tbl_IMPORT_ClientOperators with (nolock) where ownerId = and ownerType = and eai_batchId = and fseUpdateStatus is null and fseContactId is null