select iff.fieldId, iff.formatId, iff.fieldName, iff.fieldDataType, iff.fieldLength, iff.required, iff.stagingColumn, iff.stagingTransformFn, iff.fieldFormatRegEx, iff.sequence, iff.defaultValue, iff.usageComments, iff.hiddenField, iff.defaultAlways, iff.stagingColumnSave, iff.usageCommentsSave, iff.fieldFormatTestFn, iff.fieldFormatRegExFail, --substring(iff.usageComments,1,charindex('|', iff.usageComments)-1) as category ltrim( rtrim( iff.stagingColumnSave ) ) as category from tbl_EAI_InboundFileFormatFields iff with (nolock) inner join tbl_EAI_InboundFileFormats ff with (nolock) on ff.formatId = iff.formatId where ff.ownerType = and ff.ownerId = and ff.formatName = order by iff.fieldName, iff.sequence update import set importMapped = case when isNumeric(prt.#_partnerColumn#) = 1 then 'Y' else 'N' end from #_importTableName# import left outer join #_partnerTable# prt on import.fsl_id = prt.#_partnerColumn# and import.ownerid = prt.ownerid and import.ownerType = prt.#_partnerOwnerTypeColumn# where import.ownerType = and import.ownerid = and import.eai_batchid = with dups as ( select eai_batchid, fsl_id, companyName, count(*) duplicates from #_importTableName# import with (nolock) where ownerType = and ownerid = and eai_batchid = group by eai_batchid, fsl_id, companyName having count(*) > 1) update import set importMapped = 'D', importComments = 'Duplicate record found. Please check the import file.' from #_importTableName# import inner join dups on dups.eai_batchid = import.eai_batchid and dups.fsl_id = import.fsl_id where duplicates > 1 and import.eai_batchid = and import.ownerid = and import.ownerType = drop table if exists [#_tempFlexFieldTable#]; with sourceData as ( select eai_lineNumber, #PreserveSingleQuotes( _completeColumnList )# from #_importTableName# import with (nolock) where import.ownerType = and import.ownerid = and import.eai_batchid = and import.importMapped = 'Y' ), sourceFields as ( select f.fieldId, f.category, f.name, f.label, f.type as fieldType from tbl_#_recordType#_Fields f with (nolock) where f.ownerType = and f.ownerid = ), rawData as ( select distinct sourceData.eai_lineNumber, sourceData.companyName, sourceData.fsl_Id, label, '#_colName#' as fieldString, '#_category#' as category, '#_field#' as field, f.fieldId, cast(#_colName# as varchar(1024) ) as fieldValue, f.fieldType, coalesce(fl.sortRank,1) as valueIndex, fv.valueId current_valueId, fv.data current_value from sourceData inner join sourceFields f on f.category = '#_category#' and f.name = '#_field#' left outer join tbl_#_recordType#_FieldLookUps fl with (nolock) on fl.fieldName = '#_field#' and selectionValue = sourceData.#_colName# left outer join tbl_#_recordType#_FieldValues fv with (nolock) on f.fieldId = fv.fieldId and fv.partnerId = sourceData.fsl_id and fv.partnerType = '#_recordType#' and fv.data = sourceData.#_colName# union all ) select * into #_tempFlexFieldTable# from rawData select eai_lineNumber, companyName, fsl_id, fieldString, category, field, fieldId, fieldValue, fieldType, valueIndex, label, valueIndex, current_valueId, current_value from #_tempFlexFieldTable# with (nolock) order by eai_lineNumber, companyName, fieldString asc select distinct fieldId, field, fieldString, fieldType, '#_recordType#' as partnerType, category from qmd_getimportedFlexData where UPPER(fieldType) IN ('CHECKBOX','SELECT') select selectionValue, sortRank from tbl_#_recordType#_FieldLookUps with (nolock) where fieldName = and ownerType = and ownerId = order by sortRank update tbl_#_recordType#_FieldValues set data = where partnerType = and partnerId = and fieldId = insert into tbl_#_recordType#_FieldValues(fieldId, partnerId, partnerType, data, valueIndex) values (, , , , ) delete from tbl_#_recordType#_FieldValues where partnerType = and partnerId = and fieldId = select cast(count(*) as varchar) + ' #_recordLabel# partner flex data sets have been loaded.' as importComments from #_importTableName# import with (nolock) where ownerType = and ownerid = and eai_batchid = and importMapped = 'Y' select import.fsl_id, import.companyname from #_importTableName# import with (nolock) where ownerType = and ownerid = and eai_batchid = and importMapped = 'N' select import.eai_lineNumber, import.fsl_id, import.companyname, import.importComments from #_importTableName# import with (nolock) where ownerType = and ownerid = and eai_batchid = and importMapped = 'D' order by import.companyname, import.eai_lineNumber desc select lineNumber, companyName, columnName, category, fieldLabel, uploadedValue, validValues from qmd_dataExceptions where 1=1 order by lineNumber, companyName, columnName, category, fieldLabel, uploadedValue, validValues drop table if exists [#_tempFlexFieldTable#];