Populating fixed destination table with different source tables

Question

Post reply

Populating fixed destination table with different source tables

Abu Dina

SSChampion

Points: 14155
More actions
March 7, 2013 at 2:25 am

#394327

I have a stored procedure which basically takes a table, performs some data manipulation then inserts the data into a destination table. Sounds straightforward but there's a more to it than that. Let me explain starting with the below image:
The destination table does not change so it will always have these 5 columns.
The source table however may have the structure as above or it may come in with fewer columns.
My business rules for populating the destination table is like this:
Column 1 = Column A + Column B
Column 2 = Column B
Column 3 = Column A + Column C
Column 4 = Column A + Column B + Column D
Column 5 = Column D
I've solved this problem by using multiple IF statements (code below) but I'm wondering if others have different ways of doing this?
CREATE PROCEDURE [dbo].[GenerateKeys_DEV] (
-- Version 24
@SOURCETABLE VARCHAR(50)
,@ID VARCHAR(50) = NULL
-- Name Details
,@TITLE VARCHAR(50) = NULL
,@FULLCONTACTNAME VARCHAR(50) = NULL
,@INITIAL VARCHAR(50) = NULL
,@FORENAME VARCHAR(50) = NULL
,@MIDDLENAME VARCHAR(50) = NULL
,@SURNAME VARCHAR(50) = NULL
-- Address Details
,@BUILDINGNAME VARCHAR(50) = NULL
,@ADDRESS1 VARCHAR(50) = NULL
,@ADDRESS2 VARCHAR(50) = NULL
,@ADDRESS3 VARCHAR(50) = NULL
,@ADDRESS4 VARCHAR(50) = NULL
,@ADDRESS5 VARCHAR(50) = NULL
,@TOWN VARCHAR(50) = NULL
,@COUNTY VARCHAR(50) = NULL
,@POSTCODE VARCHAR(50) = NULL
,@POSTCODEPREFIX VARCHAR(50) = NULL
,@POSTCODESUFFIX VARCHAR(50) = NULL
,@COUNTRY VARCHAR(50) = NULL
-- Company Details
,@ORGANISATIONNAME VARCHAR(50) = NULL
,@COMPANYNAME2 VARCHAR(50) = NULL
,@COMPANYNAME3 VARCHAR(50) = NULL
,@COMPANYNAME4 VARCHAR(50) = NULL
-- Other Details
,@EMAIL VARCHAR(50) = NULL
,@HOMETELEPHONE VARCHAR(50) = NULL
,@MOBILETELEPHONE VARCHAR(50) = NULL
,@FAX VARCHAR(50) = NULL
,@TELEPHONEAREACODE VARCHAR(50) = NULL
,@LANGUAGECODE VARCHAR(50) = NULL
,@WEBSITE VARCHAR(50) = NULL
,@CUSTOMERURN VARCHAR(50) = NULL
,@CUSTOMERURN2 VARCHAR(50) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @MSG NVARCHAR(500)
-- 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) = ''
-- Name name details
DECLARE @mkTitleSELECTString VARCHAR(800) = ''''''
DECLARE @mkNameKeySELECTString VARCHAR(800) = ''''''
DECLARE @mkName1SELECTString VARCHAR(800) = ''''''
DECLARE @mkName2SELECTString VARCHAR(800) = ''''''
DECLARE @mkName3SELECTString VARCHAR(800) = ''''''
DECLARE @mkNormalisedName VARCHAR(800) = ''''''
-- Company specific keys
DECLARE @mkOrgNameKeyString VARCHAR(800) = ''''''
DECLARE @mkOrgNameSELECTString VARCHAR(800) = ''''''
DECLARE @mkOrgName1SELECTString VARCHAR(800) = ''''''
DECLARE @mkOrgName2SELECTString VARCHAR(800) = ''''''
DECLARE @mkOrgName3SELECTString VARCHAR(800) = ''''''
DECLARE @mkNormalisedOrganisation VARCHAR(800) = ''''''
-- Address specific details
DECLARE @mkPostInSELECTString NVARCHAR(800) = ''''''
DECLARE @mkPostOutSELECTString NVARCHAR(800) = ''''''
DECLARE @mkPhoneticStreetSELECTString NVARCHAR(800) = ''''''
DECLARE @mkPremiseSELECTString NVARCHAR(800) = ''''''
DECLARE @mkTownSELECTString NVARCHAR(800) = ''''''
DECLARE @mkAddressKeySELECTString NVARCHAR(800) = ''''''
-- Email and Telephone Numbers
DECLARE @mkEmailSELECTString NVARCHAR(800) = ''''''
DECLARE @mkTelephoneSELECTString NVARCHAR(800) = ''''''
DECLARE @mkMobileSELECTString NVARCHAR(800) = ''''''
-- Match Keys (this is to capture matches that are produced by the current merge routines
DECLARE @MatchKeyType1 VARCHAR(MAX) = ''
IF @TITLE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @TITLE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @TITLE = NULL
IF @FULLCONTACTNAME IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @FULLCONTACTNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @FULLCONTACTNAME = NULL
IF @INITIAL IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @INITIAL AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @INITIAL = NULL
IF @TITLE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @TITLE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @TITLE = NULL
IF @FORENAME IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @FORENAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @FORENAME = NULL
IF @MIDDLENAME IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @MIDDLENAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @MIDDLENAME = NULL
IF @SURNAME IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @SURNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @SURNAME = NULL
IF @BUILDINGNAME IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @BUILDINGNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @BUILDINGNAME = NULL
IF @ADDRESS1 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS1 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS1 = NULL
IF @ADDRESS2 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS2 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS2 = NULL
IF @ADDRESS3 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS3 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS3 = NULL
IF @ADDRESS4 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS4 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS4 = NULL
IF @ADDRESS5 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ADDRESS5 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ADDRESS5 = NULL
IF @TOWN IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @TOWN AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @TOWN = NULL
IF @COUNTY IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COUNTY AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COUNTY = NULL
IF @POSTCODE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @POSTCODE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @POSTCODE = NULL
IF @POSTCODEPREFIX IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @POSTCODEPREFIX AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @POSTCODEPREFIX = NULL
IF @POSTCODESUFFIX IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @POSTCODESUFFIX AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @POSTCODESUFFIX = NULL
IF @COUNTRY IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COUNTRY AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COUNTRY = NULL
IF @ORGANISATIONNAME IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @ORGANISATIONNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @ORGANISATIONNAME = NULL
IF @COMPANYNAME2 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COMPANYNAME2 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COMPANYNAME2 = NULL
IF @COMPANYNAME3 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COMPANYNAME3 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COMPANYNAME3 = NULL
IF @COMPANYNAME4 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @COMPANYNAME4 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @COMPANYNAME4 = NULL
IF @EMAIL IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @EMAIL AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @EMAIL = NULL
IF @HOMETELEPHONE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @HOMETELEPHONE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @HOMETELEPHONE = NULL
IF @MOBILETELEPHONE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @MOBILETELEPHONE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @MOBILETELEPHONE = NULL
IF @FAX IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @FAX AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @FAX = NULL
IF @TELEPHONEAREACODE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @TELEPHONEAREACODE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @TELEPHONEAREACODE = NULL
IF @LANGUAGECODE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @LANGUAGECODE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @LANGUAGECODE = NULL
IF @website IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @website AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @website = NULL
IF @CUSTOMERURN IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @CUSTOMERURN AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @CUSTOMERURN = NULL
IF @CUSTOMERURN2 IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = @CUSTOMERURN2 AND OBJECT_ID = OBJECT_ID(@SOURCETABLE)) SET @CUSTOMERURN2 = NULL
-- Main sproc logic:
IF @SOURCETABLE IS NULL
BEGIN
RAISERROR('No source table specified', 16, 1);
RETURN
END
IF @SOURCETABLE IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @SOURCETABLE)
BEGIN
RAISERROR('Source table doesn''t exist!', 16, 1);
RETURN
END
-- If no ID column is supplied then we can't really perform any processing!
IF @ID IS NULL
BEGIN
RAISERROR('No ID column specified!', 16, 1);
RETURN
END
DECLARE @FullNameForProcessing NVARCHAR(255) = ''
DECLARE @FullAddressForProcessing NVARCHAR(1000) = ''
DECLARE @AddressStreetPart NVARCHAR(1000) = ''
-- If we already have a full contact name then we can just use this to generate our various name parts
IF @FULLCONTACTNAME IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @FULLCONTACTNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @FullNameForProcessing = 'ISNULL(' + @FULLCONTACTNAME + ', '''')'
END
ELSE
BEGIN
SET @FullNameForProcessing = 'ISNULL(' + ISNULL(@INITIAL, '''''') + ', '''') + '' '' + ISNULL(' + ISNULL(@FORENAME, '''''')
+ ', '''') + '' '' + ISNULL(' + ISNULL(@MIDDLENAME, '''''') + ', '''') + '' '' + ISNULL(' + ISNULL(@SURNAME, '''''') + ', '''')'
END
-- mkNameKey = Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + ')) + LEFT(dbo.clrFn_GetFirstWord(' + @FullNameForProcessing + '), 1)'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + '))'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(' + @FullNameForProcessing + '))'
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetSecondWord(' + @FullNameForProcessing + '))'
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = 'UPPER(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + ')) + '','' + UPPER(dbo.clrFn_GetFirstWord(' + @FullNameForProcessing + ')) + '','' + UPPER(dbo.clrFn_GetSecondWord(' + @FullNameForProcessing + '))'
IF @ORGANISATIONNAME IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @ORGANISATIONNAME AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @mkOrgNameKeyString = 'dbo.clrFn_CompanyNameSplitter(' + @ORGANISATIONNAME + ', 1) + '' '' + dbo.clrFn_CompanyNameSplitter(' + @ORGANISATIONNAME + ', 2)'
SET @mkNormalisedOrganisation = 'dbo.clrFn_CoNameMatch_String(' + @ORGANISATIONNAME + ')'
SET @mkOrgName1SELECTString = 'dbo.clrFn_CompanyNameSplitter(' + @ORGANISATIONNAME + ', 1)'
SET @mkOrgName2SELECTString = 'dbo.clrFn_CompanyNameSplitter(' + @ORGANISATIONNAME + ', 2)'
SET @mkOrgName3SELECTString = 'dbo.clrFn_CompanyNameSplitter(' + @ORGANISATIONNAME + ', 3)'
END
ELSE
BEGIN
SET @mkOrgNameKeyString = ''''''
SET @mkNormalisedOrganisation = ''',,'''
SET @mkOrgName1SELECTString = ''''''
SET @mkOrgName2SELECTString = ''''''
SET @mkOrgName3SELECTString = ''''''
END
IF @POSTCODE IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @POSTCODE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
-- We need to know if we're dealing with UK or International postcodes
IF @COUNTRY IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @COUNTRY AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @mkPostOutSELECTString = 'CASE WHEN ' + @COUNTRY + ' = ''UNITED KINGDOM'' THEN dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 1) ELSE REPLACE(' + @COUNTRY + ' + ' + @POSTCODE + ', '' '', '''') END'
SET @mkPostInSELECTString = 'CASE WHEN ' + @COUNTRY + ' = ''UNITED KINGDOM'' THEN dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 2) ELSE REPLACE(' + @COUNTRY + ' + ' + @POSTCODE + ', '' '', '''') END'
END
ELSE
BEGIN
SET @mkPostOutSELECTString = 'dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 1)'
SET @mkPostInSELECTString = 'dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 2)'
END
END
ELSE
BEGIN
SET @mkPostOutSELECTString = ''''''
SET @mkPostInSELECTString = ''''''
END
-- Complete Address
SET @FullAddressForProcessing = 'LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(' + ISNULL(@BUILDINGNAME, '''''') + ', '''') + '' '' + ISNULL(' + ISNULL(@ADDRESS1, '''''') + ', '''') + '' '' + ISNULL(' + ISNULL(@ADDRESS2, '''''')
+ ', '''') + '' '' + ISNULL(' + ISNULL(@ADDRESS3, '''''') + ', '''') + '' '' + ISNULL(' + ISNULL(@ADDRESS4, '''''') + ', '''') + '' '' + ISNULL(' + ISNULL(@ADDRESS5, '''''') +
', ''''), '' '',''<>''),''><'',''''),''<>'','' '')))'
-- For Street name we will assume that it's made up of BuildingName and Address1
SET @AddressStreetPart = 'LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ISNULL(' + ISNULL(@BUILDINGNAME, '''''') + ', '''') + '' '' + ISNULL(' + ISNULL(@ADDRESS1, '''''') + ', ''''), '' '',''<>''),''><'',''''),''<>'','' '')))'
SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_RemoveDigits(' + @AddressStreetPart + '))'
SET @mkPremiseSELECTString = 'dbo.clrFn_GetDigits(' + @FullAddressForProcessing + ')'
SET @mkAddressKeySELECTString = 'UPPER(' + @FullAddressForProcessing + ')'
IF @TOWN IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @TOWN AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @mkTownSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_RemoveDigits(' + @TOWN + '))'
END
IF @EMAIL IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @EMAIL AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @mkEmailSELECTString = 'ISNULL(' + @EMAIL + ', '''')'
END
IF @HOMETELEPHONE IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @HOMETELEPHONE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @mkTelephoneSELECTString = 'ISNULL(' + @HOMETELEPHONE + ', '''')'
END
IF @MOBILETELEPHONE IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @MOBILETELEPHONE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @mkMobileSELECTString = 'ISNULL(' + @MOBILETELEPHONE + ', '''')'
END
IF @TITLE IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE name = @TITLE AND OBJECT_ID = OBJECT_ID(@SOURCETABLE))
BEGIN
SET @mkTitleSELECTString = 'UPPER(ISNULL(' + @TITLE + ', ''''))'
END
SET @InsertIntoPart = 'INSERT INTO dbo.' + @SourceTable + '_keys_ (ID, GUID, mkTitle, mkNameKey, mkAddressKey, mkName1, mkName2, mkName3, mkNormalizedName, mkOrganizationKey,
mkNormalizedOrganization, mkOrgName1, mkOrgName2, mkorgName3, mkPostIn, mkPostOut, mkPhoneticStreet, mkPremise, mkPhoneticTown, mkEmailAddress,
mkTelephoneNumber, mkMobileNumber)'
SET @SelectPart = 'SELECT ' + @ID + ', GUID' + ', ' + @mkTitleSELECTString + ', ' + @mkNameKeySELECTString + ', ' + @mkAddressKeySELECTString + ', ' +
@mkName1SELECTString + ', ' + @mkName2SELECTString + ', ' + @mkName3SELECTString + ',' + @mkNormalisedName + ','
+ @mkOrgNameKeyString + ', ' + @mkNormalisedOrganisation +
', ' + @mkOrgName1SELECTString + ', ' + @mkOrgName2SELECTString + ', ' + @mkOrgName3SELECTString + ', ' + @mkPostInSELECTString + ', ' + @mkPostOutSELECTString +
', ' + @mkPhoneticStreetSELECTString + ', ' + @mkPremiseSELECTString + ', ' + @mkTownSELECTString + ', ' + @mkEmailSELECTString + ', ' + @mkTelephoneSELECTString
+ ', ' + @mkMobileSELECTString
SET @FromPart = 'FROM dbo.' + @SourceTable
EXEC (@InsertIntoPart + ' ' + @SelectPart + ' ' + @FromPart )
EXEC('DELETE FROM dbo.' + @SourceTable + '_keys_
WHERE LEN(LTRIM(RTRIM(mkNameKey) )) = 0
AND LEN(LTRIM(RTRIM(mkAddressKey) )) = 0
AND LEN(LTRIM(RTRIM(mkPostOut) )) = 0
AND LEN(LTRIM(RTRIM(mkPostIn) )) = 0')
-- Probably not the most efficient way to do this but it will do for now:
-- Exact Match Key Type 1
EXEC('UPDATE a
SET a.mkMatchKeyType1 = HASHBYTES(''SHA2_512'', UPPER(REPLACE(LEFT(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + '), 5) + dbo.clrFn_GetFirstWord(' + @FullNameForProcessing + ') + ' + @mkPostOutSELECTString +
' + ' + @mkPostInSELECTString + ' + LEFT(dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetLastWord(' +
@mkNormalisedOrganisation + '), 12) + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
+ @ADDRESS1 + ', ''flat '', ''''), ''apartment '', ''''), ''apt '', ''''), '' ave'', ''''), '' street'', ''''), '' st'', ''''), '' road'', ''''), '' rd'', ''''),
'' avenue'', ''''), '' lane'', ''''), ''-'', '' ''), ''the'', ''''), ''.'', ''''), '','', ''''), ''('', ''''), '')'', ''''), '''''''', ''''), ''&'', ''''), '' and '', '' ''), '' limited'', ''''), '' ltd'', ''''), '' plc'', ''''), '' crescent'', ''''), ''/'', '' ''), 12), '' '', '''')))
FROM dbo.' + @SourceTable + '_keys_ AS a
INNER JOIN dbo.' + @SourceTable + ' AS b
ON a.ID = b.ID
WHERE LEN(' + @FullNameForProcessing + ') > 0
AND LEN(' + @mkPostOutSELECTString + ') > 0
AND LEN(' + @mkPostInSELECTString + ') > 0
AND LEN(REPLACE(' + @mkNormalisedOrganisation + ', '','', '''')) > 0
AND LEN(' + @ADDRESS1 + ') > 0')
-- Exact Matching Key Type 2
EXEC('UPDATE a
SET a.mkMatchKeyType2 = HASHBYTES(''SHA2_512'', UPPER(REPLACE(LEFT(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + '), 5) + dbo.clrFn_GetFirstWord(' + @FullNameForProcessing + ') + ' + @mkPostOutSELECTString +
' + ' + @mkPostInSELECTString + ' + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
+ @ADDRESS1 + ', ''flat '', ''''), ''apartment '', ''''), ''apt '', ''''), '' ave'', ''''), '' street'', ''''), '' st'', ''''), '' road'', ''''), '' rd'', ''''),
'' avenue'', ''''), '' lane'', ''''), ''-'', '' ''), ''the'', ''''), ''.'', ''''), '','', ''''), ''('', ''''), '')'', ''''), '''''''', ''''), ''&'', ''''), '' and '', '' ''), '' limited'', ''''), '' ltd'', ''''), '' plc'', ''''), '' crescent'', ''''), ''/'', '' ''), 12), '' '', '''')))
FROM dbo.' + @SourceTable + '_keys_ AS a
INNER JOIN dbo.' + @SourceTable + ' AS b
ON a.ID = b.ID
WHERE LEN(' + @FullNameForProcessing + ') > 0
AND LEN(' + @mkPostOutSELECTString + ') > 0
AND LEN(' + @mkPostInSELECTString + ') > 0
AND LEN(' + @ADDRESS1 + ') > 0')
-- Exact Matching Key Type 3
EXEC('UPDATE a
SET a.mkMatchKeyType3 = HASHBYTES(''SHA2_512'',UPPER(REPLACE(LEFT(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + '), 5) + LEFT(dbo.clrFn_GetFirstWord(' + @FullNameForProcessing + '), 1) + ' + @mkTitleSELECTString + ' + ' + @mkPostOutSELECTString +
' + ' + @mkPostInSELECTString + ' + LEFT(dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetLastWord(' +
@mkNormalisedOrganisation + '), 12) + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
+ @ADDRESS1 + ', ''flat '', ''''), ''apartment '', ''''), ''apt '', ''''), '' ave'', ''''), '' street'', ''''), '' st'', ''''), '' road'', ''''), '' rd'', ''''),
'' avenue'', ''''), '' lane'', ''''), ''-'', '' ''), ''the'', ''''), ''.'', ''''), '','', ''''), ''('', ''''), '')'', ''''), '''''''', ''''), ''&'', ''''), '' and '', '' ''), '' limited'', ''''), '' ltd'', ''''), '' plc'', ''''), '' crescent'', ''''), ''/'', '' ''), 12), '' '', '''')))
FROM dbo.' + @SourceTable + '_keys_ AS a
INNER JOIN dbo.' + @SourceTable + ' AS b
ON a.ID = b.ID
WHERE LEN(' + @FullNameForProcessing + ') > 0
AND LEN(' + @mkPostOutSELECTString + ') > 0
AND LEN(' + @mkPostInSELECTString + ') > 0
AND LEN(REPLACE(' + @mkNormalisedOrganisation + ', '','', '''')) > 0
AND LEN(' + @ADDRESS1 + ') > 0')
-- Exact Matching Key Type 4
EXEC('UPDATE a
SET a.mkMatchKeyType4 = HASHBYTES(''SHA2_512'',UPPER(REPLACE(LEFT(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + '), 6) + ' + @mkTitleSELECTString + ' + ' + @mkPostOutSELECTString +
' + ' + @mkPostInSELECTString + ' + LEFT(dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetLastWord(' +
@mkNormalisedOrganisation + '), 12) + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
+ @ADDRESS1 + ', ''flat '', ''''), ''apartment '', ''''), ''apt '', ''''), '' ave'', ''''), '' street'', ''''), '' st'', ''''), '' road'', ''''), '' rd'', ''''),
'' avenue'', ''''), '' lane'', ''''), ''-'', '' ''), ''the'', ''''), ''.'', ''''), '','', ''''), ''('', ''''), '')'', ''''), '''''''', ''''), ''&'', ''''), '' and '', '' ''), '' limited'', ''''), '' ltd'', ''''), '' plc'', ''''), '' crescent'', ''''), ''/'', '' ''), 12), '' '', '''')))
FROM dbo.' + @SourceTable + '_keys_ AS a
INNER JOIN dbo.' + @SourceTable + ' AS b
ON a.ID = b.ID
WHERE LEN(' + @FullNameForProcessing + ') > 0
AND LEN(' + @mkPostOutSELECTString + ') > 0
AND LEN(' + @mkPostInSELECTString + ') > 0
AND LEN(REPLACE(' + @mkNormalisedOrganisation + ', '','', '''')) > 0
AND LEN(' + @ADDRESS1 + ') > 0
AND LEN(' + @mkTitleSELECTString + ') > 0')
-- Exact Match Key Type 5
EXEC('UPDATE a
SET a.mkMatchKeyType5 = HASHBYTES(''SHA2_512'', UPPER(REPLACE(LEFT(dbo.clrFn_GetLastWord(' + @FullNameForProcessing + '), 5) + dbo.clrFn_GetFirstWord(' + @FullNameForProcessing + ') + ' + @mkPostOutSELECTString +
' + ' + @mkPostInSELECTString + ' + LEFT(dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetFirstWord(' + @mkNormalisedOrganisation + ') + dbo.clrFn_GetLastWord(' +
@mkNormalisedOrganisation + '), 12), '' '', '''')))
FROM dbo.' + @SourceTable + '_keys_ AS a
INNER JOIN dbo.' + @SourceTable + ' AS b
ON a.ID = b.ID
WHERE LEN(' + @FullNameForProcessing + ') > 0
AND LEN(' + @mkPostOutSELECTString + ') > 0
AND LEN(' + @mkPostInSELECTString + ') > 0
AND LEN(REPLACE(' + @mkNormalisedOrganisation + ', '','', '''')) > 0
AND LEN(' + @mkTitleSELECTString + ') = 0')
END;
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply