IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
		AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')
			BEGIN
				-- Phonetic Surname + First letter of Forename
				SET @mkNameKeySELECTString = 'dbo.DoubleMetaPhone(dbo.GetLastWord(Surname) + LEFT(dbo.GetFirstWord(Forename), 1))'
				
				-- Phonetic Surname
				SET @mkName1SELECTString = 'dbo.DoubleMetaPhone(Surname)'
				
				-- Phonetic Forename
				SET @mkName2SELECTString = 'dbo.DoubleMetaPhone(dbo.GetFirstWord(Forename))'
				
				-- Phonetic Middle name or Initial
				SET @mkName3SELECTString = 'dbo.DoubleMetaPhone(dbo.GetSecondWord(Forename))'
				
				-- Normalised name = Surname + Forename + Middle Name
				SET @mkNormalisedName = 'dbo.GetLastWord(Surname) + '','' + dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'
			
			END	

CREATE PROCEDURE [dbo].[usp_GenerateKeys]
	@SourceTable VARCHAR(50)
AS
	BEGIN
		
		SET NOCOUNT ON
		
		DECLARE @SQL NVARCHAR(MAX)
		DECLARE @MSG NVARCHAR(500)
	
		-- Get source table unique reference
		
		IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'FieldMappings')
		DROP TABLE dbo.FieldMappings
		
		select Label, FieldOrder into  dbo.FieldMappings
		from [Snapshot_MeritDirect].dbo.BFieldTypes
		WHERE Label in (select column_name from Snapshot_MeritDirect_Load.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @SourceTable)
		
		-- We need a table to hold all the fields and their definsitions, then based on what columns
		-- the staging file has, we build the keys table
	
		DECLARE @SourceTableUniqueReference VARCHAR(50)
		
		-- Used to generate the insert into the keys table	
		DECLARE @InsertIntoPart VARCHAR(4000) = ''
		DECLARE @SelectPart VARCHAR(4000) = ''
		DECLARE @FromPart VARCHAR(4000) = ''
		DECLARE @CROSSAPPLY VARCHAR(200) = ''
		
		-- Person name details
		DECLARE @mkNameKeySELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkName1SELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkName2SELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkName3SELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkNormalisedName VARCHAR(800) = 'NULL'
		
		-- Company specific keys
		DECLARE @mkOrgNameKeyString VARCHAR(800) = 'NULL'
		DECLARE @mkOrgNameSELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkOrgName1SELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkOrgName2SELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkOrgName3SELECTString VARCHAR(800) = 'NULL'
		DECLARE @mkNormalisedOrganisation VARCHAR(800) = 'NULL'
		
		-- Address specific details
		
		DECLARE @mkPostInSELECTString NVARCHAR(800) = 'NULL'
		DECLARE @mkPostOutSELECTString NVARCHAR(800) = 'NULL'
		DECLARE @mkPhoneticStreetSELECTString NVARCHAR(800) = 'NULL'
		DECLARE @mkPremiseSELECTString NVARCHAR(800) = 'NULL'
		DECLARE @mkTownSELECTString NVARCHAR(800) = 'NULL'
		
		SET @SourceTableUniqueReference = 'ID, GUID '
		
				
		---- If we already have First and Last names then there is no need to split the names
		IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
		AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')
			BEGIN
				-- Phonetic Surname + First letter of Forename
				SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname) + LEFT(dbo.GetFirstWord(Forename), 1))'
				
				-- Phonetic Surname
				SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(Surname)'
				
				-- Phonetic Forename
				SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(Forename))'
				
				-- Phonetic Middle name or Initial
				SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(Forename))'
				
				-- Normalised name = Surname + Forename + Middle Name
				SET @mkNormalisedName = 'dbo.GetLastWord(Surname) + '','' + dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'
			
			END	
			
		else 	IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
		AND NOT EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')

			BEGIN
				
				-- Phonetic Surname + First letter of Forename
				SET @mkNameKeySELECTString = 'NULL'
				
				-- Phonetic Surname
				SET @mkName1SELECTString = 'NULL'
				
				-- Phonetic Forename
				SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(Forename))'
				
				-- Phonetic Middle name or Initial
				SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(Forename))'
				
				-- Normalised name = Forename + Middle Name
				SET @mkNormalisedName = 'dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'
	
			END
			
		else IF NOT EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
		AND  EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')
		
			BEGIN
			
				-- Phonetic Surname + First letter of Forename
				SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname))'
				
				-- Phonetic Surname
				SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname))'
				
				-- Phonetic Forename
				SET @mkName2SELECTString = 'NULL'
					
				-- Phonetic Middle name or Initial
				SET @mkName3SELECTString = 'NULL'
				
				-- Normalised name = Surname
				SET @mkNormalisedName = 'dbo.GetLastWord(Surname) '
				
			END
		ELSE IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'FullContactName') 
			BEGIN
			
				-- Phonetic Surname + First letter of Forename
				SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(FullContactName) + LEFT(dbo.GetFirstWord(FullContactName), 1))'
				
				-- Phonetic Surname
				SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(FullContactName))'
				
				-- Phonetic Forename
				SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(FullContactName))'
				
				-- Phonetic Middle name or Initial
				SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(FullContactName))'
				
				-- Normalised name = Surname + Forename + Middle Name
				SET @mkNormalisedName = 'dbo.GetLastWord(FullContactName) + '','' + dbo.GetFirstWord(FullContactName) + '','' + dbo.GetSecondWord(FullContactName)'
			END
		ELSE
			BEGIN
				SET @MSG = N'No Forename, Surname or Full Contact Details present in the table.'
				RAISERROR(@MSG,16,1);
				RETURN
			END
			
		-- If we have compnay details then we need to break this down into 4 different keys
		-- mkOrganisation
		-- mkOrgName1
		-- mkOrgName2
		-- mkOrgName3
		
		IF   EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label =  'OrganisationName')
			BEGIN
				SET @mkOrgNameKeyString = 'dbo.clrFn_DoubleMetaphone(ISNULL(dbo.fn_NormaliseOrgName(OrganisationName, 0), ''''))'
				SET @mkNormalisedOrganisation = 'dbo.fn_NormaliseOrgName(OrganisationName, 0)'
				SET @mkOrgName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 1))'
				SET @mkOrgName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 2))'	
				SET @mkOrgName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 3))'			
		
			END
		
		IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label =  'Postcode')
		BEGIN
			SET @mkPostInSELECTString = 'dbo.fn_PostCodeSplitter(Postcode, 1)'
			SET @mkPostOutSELECTString = 'dbo.fn_PostCodeSplitter(Postcode, 2)'
		END
		
		IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label =  'Address1')
			BEGIN
				IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label =  'Buildingname')
					BEGIN
						SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Buildingname + '' '' + Address1))'
						SET @CROSSAPPLY = ' CROSS APPLY dbo.GetNumbersFromText(isnull(Buildingname, '''') + '' '' + Address1) AS a '
						SET @mkPremiseSELECTString = 'a.number' 
					END
				ELSE
					BEGIN
							SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Address1))'
							SET @CROSSAPPLY = ' CROSS APPLY dbo.GetNumbersFromText(Address1) AS a '
							SET @mkPremiseSELECTString = 'a.number' 
					END
			END
		

			IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label =  'Town')
			BEGIN
				SET @mkTownSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Town))'
			END
			
			
		SET @InsertIntoPart = 'INSERT INTO dbo.' + @SourceTable + '_keys_ (ID, GUID, mkNameKey, mkName1, mkName2, mkName3, mkNormalizedName, mkOrganizationKey, 
												mkNormalizedOrganization, mkOrgName1, mkOrgName2, mkorgName3, mkPostIn, mkPostOut, mkPhoneticStreet, mkPremise, mkPhoneticTown)'
												
												
		---- some more processing on the keys before we insert into the keys table
		
		set @mkNameKeySELECTString = 'CASE WHEN LEN(replace(' + @mkNameKeySELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkNameKeySELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkNameKeySELECTString + ', '' '', '''') END'
		set @mkName1SELECTString = 'CASE WHEN LEN(replace(' + @mkName1SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName1SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName1SELECTString + ', '' '', '''') END'
		set @mkName2SELECTString = 'CASE WHEN LEN(replace(' + @mkName2SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName2SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName2SELECTString + ', '' '', '''') END'
		set @mkName3SELECTString = 'CASE WHEN LEN(replace(' + @mkName3SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName3SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName3SELECTString + ', '' '', '''') END'
		set @mkPhoneticStreetSELECTString = 'CASE WHEN LEN(replace(' + @mkPhoneticStreetSELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkPhoneticStreetSELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkPhoneticStreetSELECTString + ', '' '', '''') END'
		set @mkTownSELECTString = 'CASE WHEN LEN(replace(' + @mkTownSELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkTownSELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkTownSELECTString + ', '' '', '''') END'
		
		
		
		SET @SelectPart = 'SELECT ' + @SourceTableUniqueReference + ', ' + @mkNameKeySELECTString + ', ' + 
										@mkNameKeySELECTString + ', ' + @mkName2SELECTString + ', ' + @mkName3SELECTString + ',' + @mkNormalisedName + ',' 
										+ @mkOrgNameKeyString + ', ' + @mkNormalisedOrganisation +
										', ' + @mkOrgName1SELECTString + ', ' + @mkOrgName2SELECTString + ', ' + @mkOrgName3SELECTString + ', ' + @mkPostInSELECTString + ', ' + @mkPostOutSELECTString + 
										', ' + @mkPhoneticStreetSELECTString + ', ' + @mkPremiseSELECTString + ', ' + @mkTownSELECTString
		
		SET @FromPart = 'FROM Snapshot_MeritDirect_Load.dbo.' + @SourceTable 
		
		
		
		EXEC (@InsertIntoPart + ' ' + @SelectPart + ' ' + @FromPart + @CROSSAPPLY)
		
		SET @SQL = ''
		SET @SQL = 'UPDATE ' + @SourceTable + '_keys_' + ' SET mkPremise = '''' WHERE mkPremise = ''0'''
		EXEC (@SQL)
		
		SET @SQL = ''
		SET @SQL = 'UPDATE ' + @SourceTable + '_keys_' + ' SET mkPremise = '''' WHERE mkPremise IS NULL'
		EXEC (@SQL)
		
		
END;