Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Staging table population - advice needed Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 9:54 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
I have a process like this:

STEP 1: Data files get loaded into a staging database.

STEP 2: Phonetic Keys table get populated based on incoming data file.

Now the Keys table I've designed never changes. It has the following columns (simplified):

RecordID, PhoneticSurname, PhoneticForename, PhoneticStreet, PostCode, Premise


The incoming data file could have any of the following columns:

Forrename, Surname, Address1, Address2, Address3, Town, PostCode

In my stored procedure which populates the keys table for the new file, I have a number of IF statements which check the column names to build the SELECT string.

For example:

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

Is this an okay approach or am I over-complicating things?


---------------------------------------------------------


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

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
Post #1415963
Posted Wednesday, February 6, 2013 4:58 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
Not sure if I explained this very well so I will try again!

I have a table with the following columns:

TABLE NAME = Phonetic_Keys

RecordID, PhoneticSurname, PhoneticForename, PhoneticStreet, PostCode, Premise


This table will be populated from incoming data which could contain the following columns:

TABLE NAME = New_Data

ID, Forrename, Surname, Address1, Address2, Address3, Town, PostCode

If my incoming file is like above then I need to build an insert statment to populate the Phonetic_Keys table.
However, the incoming file may not have a forename or a address3 so I've used a series of IF ststements to build my SELECT statment to be used to insert into the phonetic keys table.

Please see code above. Is this a good way to do it?

I seem to have many IF statmements to check for columns before I build my dynamic insert statement.

Any ideas?


---------------------------------------------------------


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

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
Post #1416413
Posted Thursday, February 7, 2013 3:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
So if the incoming data doesn't have a forename then what do you insert into the table??..Do you Insert NULL??....

I think you should read upon Coalesce.

If you still cant get it to work then get back to me here.....we'll work something out.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1416934
Posted Thursday, February 7, 2013 5:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
Abu Dina (2/6/2013)

I seem to have many IF statmements to check for columns before I build my dynamic insert statement.

Any ideas?


'If' is a conditional statement ;so, when you need it , you use it ..

If that fieldMapping table has to be searched for creating dynamic sql then you can go on with it ;

another way could be , since you are not executing the sql's , and I suppose using the same variable elsewhere and deciding which to execute , you can put the conditions on executing the dynamic sql rather than setting the value to variables.


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1416980
Posted Thursday, February 7, 2013 5:42 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
vinu512 (2/7/2013)
So if the incoming data doesn't have a forename then what do you insert into the table??..Do you Insert NULL??....


Yes that's right, where I define the variables I have them default to = 'NULL'
so if this IF the statment is not valid, i.e. no forename then the SELECT variable for the forename will be NULL.


---------------------------------------------------------


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

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
Post #1417004
Posted Thursday, February 7, 2013 5:44 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.


---------------------------------------------------------


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

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
Post #1417007
Posted Thursday, February 7, 2013 8:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
Abu Dina (2/7/2013)
Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.


If you could post the code , may be we can work it out ...


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1417442
Posted Thursday, February 7, 2013 9:12 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
demonfox (2/7/2013)
Abu Dina (2/7/2013)
Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.


If you could post the code , may be we can work it out ...


+1
Please post the Insert statements you are working with currently.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1417453
Posted Friday, February 8, 2013 2:56 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
As requested here is the complete sproc:


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;




---------------------------------------------------------


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

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
Post #1417546
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse