February 25, 2013 at 3:25 am
Please consider the following process:
I have a source tables in the database which contain contact information such as name and address details. The structire of the source data varies like the below:
Firstname, Middlename, Surname, Address1, Address2, Town, Postcode OR
Firstname, Surname, Address1, Address2, Address3, Town, Postcode OR
Full Name, Address1, Town County, Postcode
I also have a stored procedure which accepts the source table name as a parameter and then populates a pre-defined table. This table contains columns used for the names part are as follows:
Normalized_Name, Name_Key, Phonetic_Surname, Phonetic_Forename, Phonetic_MiddleName
Normalizedname = SURNAME, FIRSTNAME, MIDDLENAME -- this is a comma seperated string of the name parts
NameKey = SURNAME + INITIAL
Phonetic_Surname = SOUNDEX(SURNAME)
Phonetic_Forename = SOUNDEX(Firstname)
Phonetic_MiddleName = SOUNDEX(MIddlename)
The INSERT statement into this table needs to be generated automatically. So what's the best way to do this?
At the moment I'm using many IF statments which build my INSERT INTO Target SELECT .. FROm Source
Hope this makes sense?
ALTER PROCEDURE [dbo].[usp_GenerateKeys_V2] (
@SourceTable VARCHAR(50)
,@ID VARCHAR(50) = NULL
,@TITLE VARCHAR(50) = NULL
,@FULLCONTACTNAME VARCHAR(50) = NULL
,@FORENAME VARCHAR(50) = NULL
,@MIDDLENAME VARCHAR(50) = NULL
,@SURNAME VARCHAR(50) = NULL
,@ADDRESS1 VARCHAR(50) = NULL
,@ADDRESS2 VARCHAR(50) = NULL
,@ADDRESS3 VARCHAR(50) = NULL
,@TOWN VARCHAR(50) = NULL
,@COUNTY VARCHAR(50) = NULL
,@POSTCODE VARCHAR(50) = NULL
,@COUNTRY VARCHAR(50) = NULL
,@EMAIL 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) = ''
-- 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'
DECLARE @mkAddressKeySELECTString NVARCHAR(800) = 'NULL'
-- Email and Telephone Numbers
DECLARE @mkEmailSELECTString NVARCHAR(800) = 'NULL'
-- 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
-- Forename and Surname supplied:
IF @FORENAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @FORENAME)
AND @SURNAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @SURNAME)
BEGIN
-- mkNameKey = Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @SURNAME + ')) + LEFT(' + @FORENAME + ', 1)'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @SURNAME + '))'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(' + @FORENAME + '))'
IF @MIDDLENAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @MIDDLENAME)
BEGIN
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(' + @MIDDLENAME + '))'
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = 'UPPER(dbo.clrFn_GetLastWord(' + @SURNAME + ')) + '','' + UPPER(dbo.clrFn_GetFirstWord(' + @FORENAME + ')) + '','' + UPPER(dbo.clrFn_GetFirstWord(' + @MIDDLENAME + '))'
END
ELSE
BEGIN
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetSecondWord(' + @FORENAME + '))'
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = 'UPPER(dbo.clrFn_GetLastWord(' + @SURNAME + ')) + '','' + UPPER(dbo.clrFn_GetFirstWord(' + @FORENAME + ')) + '','' + UPPER(dbo.clrFn_GetSecondWord(' + @FORENAME + '))'
END
END
-- Forename but no Surname supplied
ELSE IF @FORENAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @FORENAME)
AND @SURNAME IS NULL
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'NULL'
-- Phonetic Surname
SET @mkName1SELECTString = 'NULL'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(' + @FORENAME + '))'
IF @MIDDLENAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @MIDDLENAME)
BEGIN
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(' + @MIDDLENAME + '))'
-- Normalised name = Forename + Middle Name
SET @mkNormalisedName = ''','' + UPPER(dbo.clrFn_GetFirstWord(' + @FORENAME + ')) + '','' + UPPER(dbo.clrFn_GetFirstWord(' + @MIDDLENAME + '))'
END
ELSE
BEGIN
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetSecondWord(' + @FORENAME + '))'
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = ''','' + UPPER(dbo.clrFn_GetFirstWord(' + @FORENAME + ')) + '','' + UPPER(dbo.clrFn_GetSecondWord(' + @FORENAME + '))'
END
END
-- Surname but no forename supplied
ELSE IF @SURNAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @SURNAME)
AND @FORENAME IS NULL
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @SURNAME + '))'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @SURNAME + '))'
-- Phonetic Forename
SET @mkName2SELECTString = ''''''
IF @MIDDLENAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @MIDDLENAME)
BEGIN
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(' + @MIDDLENAME + '))'
-- Normalised name = Forename + Middle Name
SET @mkNormalisedName = 'UPPER(dbo.clrFn_GetLastWord(' + @SURNAME + ')) + '',,'' + UPPER(dbo.clrFn_GetFirstWord(' + @MIDDLENAME + '))'
END
ELSE
BEGIN
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = ''''''
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = 'UPPER(dbo.clrFn_GetLastWord(' + @SURNAME + ')) + '',,'''
END
END
ELSE IF @FULLCONTACTNAME IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @FULLCONTACTNAME)
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @FULLCONTACTNAME + ')) + LEFT(' + @FULLCONTACTNAME + ', 1)'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(' + @FULLCONTACTNAME + '))'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(' + @FULLCONTACTNAME + '))'
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetSecondWord(' + @FULLCONTACTNAME + '))'
-- Normalised name = Surname
SET @mkNormalisedName = 'UPPER(dbo.clrFn_GetLastWord(' + @FULLCONTACTNAME + ')) + '','' + UPPER(dbo.clrFn_GetFirstWord(' + @FULLCONTACTNAME + ')) + '','' + UPPER(dbo.clrFn_GetSecondWord(' + @FULLCONTACTNAME + '))'
END
ELSE
BEGIN
-- if we have no name details then we can't really process the file!
RAISERROR('Unable to generate keys without appropriate contact details', 16, 1)
RETURN
END
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = 'OrganisationName')
BEGIN
SET @mkOrgNameKeyString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(dbo.clrFn_CoNameMatch_String(OrganisationName)))+
dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetSecondWord(dbo.clrFn_CoNameMatch_String(OrganisationName)))'
SET @mkNormalisedOrganisation = 'dbo.clrFn_CoNameMatch_String(OrganisationName)'
SET @mkOrgName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetFirstWord(dbo.clrFn_CoNameMatch_String(OrganisationName)))'
SET @mkOrgName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetSecondWord(dbo.clrFn_CoNameMatch_String(OrganisationName)))'
SET @mkOrgName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_GetLastWord(dbo.clrFn_CoNameMatch_String(OrganisationName)))'
END
IF @POSTCODE IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @POSTCODE)
BEGIN
-- We need to know if we're dealing with UK or International postcodes
IF @COUNTRY IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @COUNTRY)
BEGIN
SET @mkPostOutSELECTString = 'CASE WHEN ' + @COUNTRY + ' = ''UNITED KINGDOM'' THEN dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 1) ELSE InternationalPostcode END'
SET @mkPostInSELECTString = 'CASE WHEN ' + @COUNTRY + ' = ''UNITED KINGDOM'' THEN dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 2) ELSE InternationalPostcode END'
SET @CROSSAPPLY = 'cross apply dbo.iTVF_AddCountryCode(' + @COUNTRY + ', ' + @POSTCODE + ') as a'
END
ELSE
BEGIN
SET @mkPostOutSELECTString = 'dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 1)'
SET @mkPostInSELECTString = 'dbo.clrFn_SplitUKPostCode(' + @POSTCODE + ', 2)'
END
END
IF @ADDRESS1 IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @ADDRESS1)
BEGIN
IF @ADDRESS2 IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @ADDRESS2)
BEGIN
SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_RemoveDigits(ISNULL(' + @ADDRESS1 + ', '''') + '' '' + ISNULL(' + @ADDRESS2 + ', '''')))'
SET @mkPremiseSELECTString = 'UPPER(dbo.clrFn_GetDigits(ISNULL(' + @ADDRESS1 + ', '''') + '' '' + ISNULL(' + @ADDRESS2 + ', '''')))'
SET @mkAddressKeySELECTString = 'UPPER(replace(replace(replace(ISNULL(CAST(' + @ADDRESS1 + ' AS VARCHAR(150)), '''') + '' '' + ISNULL(CAST('
+ @ADDRESS2 + ' AS VARCHAR(150)), '''') + '' '' + ISNULL(CAST(' + @ADDRESS3 + ' AS VARCHAR(150)), ''''),'' '',''<>''),''><'',''''),''<>'','' ''))'
END
ELSE
BEGIN
SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_RemoveDigits(' + @ADDRESS1 + '))'
SET @mkPremiseSELECTString = 'UPPER(dbo.clrFn_GetDigits(' + @ADDRESS1 + '))'
END
END
IF @TOWN IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @TOWN)
BEGIN
SET @mkTownSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.clrFn_RemoveDigits(' + @TOWN + '))'
END
IF @EMAIL IS NOT NULL AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SourceTable AND COLUMN_NAME = @EMAIL)
BEGIN
SET @mkEmailSELECTString = 'Email'
END
SET @InsertIntoPart = 'INSERT INTO dbo.' + @SourceTable + '_keys_ (ID, mkNameKey, mkAddressKey, mkName1, mkName2, mkName3, mkNormalizedName, mkOrganizationKey,
mkNormalizedOrganization, mkOrgName1, mkOrgName2, mkorgName3, mkPostIn, mkPostOut, mkPhoneticStreet, mkPremise, mkPhoneticTown, mkEmailAddress)'
SET @SelectPart = 'SELECT ' + @ID + ', ' + @mkNameKeySELECTString + ', ' + @mkAddressKeySELECTString + ', ' +
@mkName1SELECTString + ', ' + @mkName2SELECTString + ', ' + @mkName3SELECTString + ',' + @mkNormalisedName + ','
+ @mkOrgNameKeyString + ', ' + @mkNormalisedOrganisation +
', ' + @mkOrgName1SELECTString + ', ' + @mkOrgName2SELECTString + ', ' + @mkOrgName3SELECTString + ', ' + @mkPostInSELECTString + ', ' + @mkPostOutSELECTString +
', ' + @mkPhoneticStreetSELECTString + ', ' + @mkPremiseSELECTString + ', ' + @mkTownSELECTString + ', ' + @mkEmailSELECTString
SET @FromPart = 'FROM dbo.' + @SourceTable
EXEC (@InsertIntoPart + ' ' + @SelectPart + ' ' + @FromPart + ' ' + @CROSSAPPLY)
END;
Target Table definistion: ColumnA, ColumnB, ColumnC and ColumnD
ColumnA =
---------------------------------------------------------
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