I'm currently in a scenario where we have a clunky, old, monolith of a database powering one or two of our less user-friendly systems (this is controlled by a third-party, so I can't change that).
Going forward, I'm looking to push the necessary data into a new (better structured) database and implement a mechanism to keep the data in sync between the two.
One of the quirky traits of this old database is that, instead of just using null values, it uses empty strings and treats them as if they're null.
In the new database, I want to use nulls where there's no data (because I can't think of any good reasons not to).
My issue is when I'm pulling the data from the old database to the new one (using MERGE statements), I'm using a NULLIF([myCol], '') to check there's actually any data - and just treating it as a null if not.
For example, when syncing the data for students I'm planning on doing something along the lines of (the name of the databases and schemas isn't relevant to the question so I'm anonymizing the structure a bit):
USE [NewDB];
DROP TABLE IF EXISTS #myTempTable;
SELECT *
INTO #myTempTable
FROM [OldDB].[schemaName].[Students];
MERGE [schema].[Person] p
USING #myTempTable s
ON TRIM(s.STUD_ISN) = p.StudentDBID
--WHEN MATCHED and not equal, update
WHEN NOT MATCHED
THEN INSERT (
[Surname] ,
[PreferredSurname] ,
[FormerSurname] ,
[Forename] ,
[MiddleNames] ,
[PreferredForename] ,
[DoB] ,
[Gender] ,
[GenderIdentity] ,
[Title] ,
[Deceased] ,
[DeceasedDate] ,
[StudentDBID])
VALUES (
NULLIF(TRIM([s].[STUD_Surname]), '') ,
NULLIF(TRIM([s].[STUD_Preferred_Surname]), '') ,
NULLIF(TRIM([s].STUD_Former_Surname), '') ,
NULLIF(TRIM([s].[STUD_Forename_1]), '') ,
NULLIF(TRIM([s].[STUD_Forename_2]), '') ,
NULLIF(TRIM([s].[STUD_Known_As]), '') ,
[s].[STUD_DOB] ,
NULLIF(TRIM([s].[STUD_Gender]), '') ,
NULLIF(TRIM([s].STUD_Gender_Identity), '') ,
NULLIF(TRIM([s].STUD_Title), '') ,
[s].STUD_Deceased ,
[s].STUD_Date_of_Death ,
TRIM([s].STUD_ISN)
);
Now, obviously this current query only deals with the following scenario:
The old database has data and the new one doesn't
Because I'm having some grief with the logic for the following scenario:
The new database has the corresponding record, but it needs updating.
For the text based fields there's 3 different scenarios where the "new" data would need updating:
I was hoping to do something relatively simple like:
WHEN MATCHED AND (
(NULLIF(TRIM([s].[STUD_Surname]), '') IS NOT NULL
AND p.Surname IS NULL)
OR (NULLIF(TRIM([s].[STUD_Surname]), '') IS NULL
AND p.Surname IS NOT NULL)
OR (NULLIF(TRIM([s].[STUD_Surname]), '') != p.Surname)
) -- do the same for the other columns
THEN UPDATE SET
Surname = s.STUD_Surname
But, NULLIF(TRIM([s].[STUD_Surname]), '') IS NOT NULL is apparently not valid?
Is my best option to just switch out the NULLIF for an ISNULL in the WHEN MATCHED clause... Or am I missing something that'll make it more efficient?
In the end, my solution was to apply the various functions within the SELECT .... Into .... block. Like so:
ALTER PROCEDURE [NGSync].[spFullStudentSync]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE IF EXISTS #students
SELECT [STUD_ISN],
NULLIF(TRIM([STUD_Student_ID]), '') AS [STUD_Student_ID] ,
NULLIF(TRIM([STUD_Surname]), '') AS [STUD_Surname] ,
NULLIF(TRIM([STUD_Forename_1]), '') AS [STUD_Forename_1] ,
NULLIF(TRIM([STUD_Forename_2]), '') AS [STUD_Forename_2] ,
NULLIF(TRIM([STUD_Known_As]), '') AS [STUD_Known_As] ,
[STUD_DOB] ,
NULLIF(TRIM([STUD_Gender]) , '') AS [STUD_Gender],
NULLIF(TRIM([STUD_Title]) , '') AS [STUD_Title],
NULLIF(TRIM([STUD_Ethnicity]) , '') AS [STUD_Ethnicity],
NULLIF(TRIM([STUD_LDDHP]) , '') AS [STUD_LDDHP],
NULLIF(TRIM([STUD_Home_Telephone_No] ) , '') AS [STUD_Home_Telephone_No] ,
NULLIF(TRIM([STUD_Daytime_Telephone] ) , '') AS [STUD_Daytime_Telephone] ,
NULLIF(TRIM([STUD_Mobile_Telephone] ) , '') AS [STUD_Mobile_Telephone] ,
NULLIF(TRIM([STUD_EMail_Address] ) , '') AS [STUD_EMail_Address] ,
NULLIF(TRIM([STUD_Former_Surname] ) , '') AS [STUD_Former_Surname] ,
CAST( CASE WHEN NULLIF(TRIM([STUD_Deceased] ) , '') = 'D' THEN 1 else 0 end AS bit ) AS [STUD_Deceased] ,
NULLIF(TRIM([STUD_Deletion_Flag] ) , '') AS [STUD_Deletion_Flag] ,
[STUD_Delete_Merge] ,
NULLIF(TRIM([STUD_Photo_filename] ) , '') AS [STUD_Photo_filename] ,
NULLIF(TRIM([STUD_Nationality] ) , '') AS [STUD_Nationality] ,
[STUD_Date_of_Entry_in_UK] ,
NULLIF(TRIM([STUD_Student_Type_FESR] ) , '') AS [STUD_Student_Type_FESR] ,
[STUD_School_ISN] ,
NULLIF(TRIM([STUD_Home_LEA] ) , '') AS [STUD_Home_LEA] ,
NULLIF(TRIM([STUD_Employer_Code] ) , '') AS [STUD_Employer_Code] ,
NULLIF(TRIM([STUD_Religion] ) , '') AS [STUD_Religion] ,
NULLIF(TRIM([STUD_Location] ) , '') AS [STUD_Location] ,
[STUD_TPS_Include] ,
[STUD_QOE_Complete] ,
NULLIF(TRIM([STUD_UCAS_Application_Code] ) , '') AS [STUD_UCAS_Application_Code] ,
[STUD_MIAP_Consent_Status] ,
[STUD_MIAP_Verification_Type] ,
NULLIF(TRIM([STUD_MIAP_Other_Verification] ) , '') AS [STUD_MIAP_Other_Verification] ,
NULLIF(TRIM([STUD_Bank_Sort_Code] ) , '') AS [STUD_Bank_Sort_Code] ,
NULLIF(TRIM([STUD_Bank_AC_No] ) , '') AS [STUD_Bank_AC_No] ,
[STUD_Bank_ISN] ,
NULLIF(TRIM([STUD_Bank_Postcode] ) , '') AS [STUD_Bank_Postcode] ,
NULLIF(TRIM([STUD_Bank_AC_Holders_Name] ) , '') AS [STUD_Bank_AC_Holders_Name] ,
NULLIF(TRIM([STUD_Current_Tutor_Group] ) , '') AS [STUD_Current_Tutor_Group] ,
NULLIF(TRIM([STUD_Current_PostCode] ) , '') AS [STUD_Current_PostCode] ,
NULLIF(TRIM([STUD_Doctor_Name] ) , '') AS [STUD_Doctor_Name] ,
NULLIF(TRIM([STUD_Doctor_Telephone] ) , '') AS [STUD_Doctor_Telephone] ,
[STUD_ULN] ,
NULLIF(TRIM([STUD_College_Email_Address] ) , '') AS [STUD_College_Email_Address] ,
[STUD_Date_Due_to_Leave_UK] ,
[STUD_UK_Residence] ,
NULLIF(TRIM([STUD_Parish] ) , '') AS [STUD_Parish] ,
NULLIF(TRIM([STUD_Area] ) , '') AS [STUD_Area] ,
[STUD_Bus_Pass] ,
NULLIF(TRIM([STUD_Bus_Route] ) , '') AS [STUD_Bus_Route] ,
[STUD_Eng_1st_Lang] ,
NULLIF(TRIM([STUD_Language] ) , '') AS [STUD_Language] ,
[STUD_High_Achiever] ,
[STUD_Is_Staff] ,
[STUD_Is_Staff_ISN] ,
[STUD_Excluded] ,
[STUD_RUI_3_no_contact] ,
[STUD_RUI_1_courses] ,
[STUD_RUI_2_surveys] ,
[STUD_PMC_C1_post] ,
[STUD_PMC_C2_phone] ,
[STUD_PMC_C3_email] ,
[STUD_Created_Date] ,
NULLIF(TRIM([STUD_Created_User] ) , '') AS [STUD_Created_User] ,
NULLIF(TRIM([STUD_Created_Prog] ) , '') AS [STUD_Created_Prog] ,
[STUD_Modified_Date] ,
NULLIF(TRIM([STUD_Modified_User] ) , '') AS [STUD_Modified_User] ,
NULLIF(TRIM([STUD_Modified_Prog] ) , '') AS [STUD_Modified_Prog] ,
[STUD_OK_to_use_Image] ,
NULLIF(TRIM([STUD_Sexual_Orientation] ) , '') AS [STUD_Sexual_Orientation] ,
NULLIF(TRIM([STUD_Gender_Identity] ) , '') AS [STUD_Gender_Identity] ,
[STUD_Visa_Proof_Produced] ,
[STUD_Visa_Proof_Produced_Date] ,
NULLIF(TRIM([STUD_Visa_Proof_Produced_Details] ) , '') AS [STUD_Visa_Proof_Produced_Details] ,
NULLIF(TRIM([STUD_Visa_Type] ) , '') AS [STUD_Visa_Type] ,
[STUD_Visa_Expiry_Date] ,
[STUD_Visa_Letter_Issued] ,
[STUD_Asylum_Seeker] ,
[STUD_Refugee] ,
[STUD_Entered_UK_for_Education] ,
[STUD_Restrictions_on_Stay] ,
NULLIF(TRIM([STUD_Add_To_Portal_Title_Bar] ) , '') AS [STUD_Add_To_Portal_Title_Bar] ,
[STUD_RUI_5] ,
NULLIF(TRIM([STUD_Visa_Reference]) , '') AS [STUD_Visa_Reference],
NULLIF(TRIM([STUD_Visa_Note] ) , '') AS [STUD_Visa_Note] ,
[STUD_FA_Bank_ISN] ,
NULLIF(TRIM([STUD_FA_Bank_Sort_Code] ) , '') AS [STUD_FA_Bank_Sort_Code] ,
NULLIF(TRIM([STUD_FA_Bank_AC_No] ) , '') AS [STUD_FA_Bank_AC_No] ,
NULLIF(TRIM([STUD_FA_Bank_AC_Holders_Name]) , '') AS [STUD_FA_Bank_AC_Holders_Name],
NULLIF(TRIM([STUD_Marital_Status] ) , '') AS [STUD_Marital_Status] ,
NULLIF(TRIM([STUD_Country_of_Birth] ) , '') AS [STUD_Country_of_Birth] ,
NULLIF(TRIM([STUD_On_Supervision] ) , '') AS [STUD_On_Supervision] ,
[STUD_S_IraqDEAS] ,
[STUD_S_Pending_SCN] ,
NULLIF(TRIM([STUD_Primary_LLDDCode] ) , '') AS [STUD_Primary_LLDDCode] ,
NULLIF(TRIM([STUD_A2C_Language] ) , '') AS [STUD_A2C_Language] ,
NULLIF(TRIM([STUD_Signature_Filename] ) , '') AS [STUD_Signature_Filename] ,
[STUD_S_ILA_Expiry_Date] ,
[STUD_Date_Of_Death] ,
[STUD_S_SQA_Exception] ,
NULLIF(TRIM([STUD_S_SQA_VID] ) , '') AS [STUD_S_SQA_VID] ,
[STUD_S_SQA_Registration_Date] ,
NULLIF(TRIM([STUD_FA_Bank_AC_Ref] ) , '') AS [STUD_FA_Bank_AC_Ref] ,
NULLIF(TRIM([STUD_Bank_AC_Ref] ) , '') AS [STUD_Bank_AC_Ref] ,
[STUD_Anonymised] ,
NULLIF(TRIM([STUD_Quarantine_Status] ) , '') AS [STUD_Quarantine_Status] ,
[STUD_KeycloakID] ,
NULLIF(TRIM([STUD_Preferred_Surname] ) , '') AS [STUD_Preferred_Surname] ,
[STUD_Exclude_From_Balancing] ,
NULLIF(TRIM([STUD_Corresp_Preference] ) , '') AS [STUD_Corresp_Preference] ,
NULLIF(TRIM([STUD_HESA_ID] ) , '') AS [STUD_HESA_ID] ,
NULLIF(TRIM([STUD_FEPUS_ID] ) , '') AS [STUD_FEPUS_ID] ,
NULLIF(TRIM([STUD_PEV_PIN] ) , '') AS [STUD_PEV_PIN] ,
NULLIF(TRIM([STUD_Photo_GUID_Filename] ) , '') AS [STUD_Photo_GUID_Filename]
INTO #students
FROM [NG].[dbo].[STUDstudent];
MERGE [people].[Person] AS [p]
USING #students AS [s]
ON [s].[STUD_ISN] = [p].[StudentDBID]
WHEN MATCHED
AND (
[s].[STUD_Surname] != [p].[Surname]
OR [s].[STUD_Preferred_Surname] != [p].[PreferredSurname]
OR [s].[STUD_Former_Surname] != [p].[FormerSurname]
OR [s].[STUD_Forename_1] != [p].[Forename]
OR [s].[STUD_Forename_2] != [p].[MiddleNames]
OR [s].[STUD_Known_As] != [p].[PreferredForename]
OR [s].[STUD_DoB] != [p].[DoB]
OR [s].[STUD_Gender] != [p].[Gender]
OR [s].[STUD_Gender_Identity] != [p].[GenderIdentity]
OR [s].[STUD_Title] != [p].[Title]
OR [s].[STUD_Deceased] != [p].[Deceased]
OR [s].[STUD_Date_of_Death] != [p].[DeceasedDate]
OR [s].[STUD_Is_Staff_ISN] != [p].[StaffDBID]
)
THEN UPDATE SET
[Surname] = [s].[STUD_Surname] ,
[PreferredSurname] = [s].[STUD_Preferred_Surname] ,
[FormerSurname] = [s].[STUD_Former_Surname],
[Forename] = [s].[STUD_Forename_1] ,
[MiddleNames] = [s].[STUD_Forename_2] ,
[PreferredForename] = [s].[STUD_Known_As],
[DoB] = [s].[STUD_DoB] ,
[Gender] = [s].[STUD_Gender] ,
[GenderIdentity] = [s].[STUD_Gender_Identity] ,
[Title] = [s].[STUD_Title] ,
[Deceased] = [s].[STUD_Deceased],
[StaffDBID] = [s].[STUD_Is_Staff_ISN]
WHEN NOT MATCHED
THEN INSERT (
[Surname] ,
[PreferredSurname] ,
[FormerSurname] ,
[Forename] ,
[MiddleNames] ,
[PreferredForename] ,
[DoB] ,
[Gender] ,
[GenderIdentity] ,
[Title] ,
[Deceased] ,
[DeceasedDate] ,
[StudentDBID] ,
[StaffDBID])
VALUES (
[s].[STUD_Surname] ,
[s].[STUD_Preferred_Surname] ,
[s].[STUD_Former_Surname] ,
[s].[STUD_Forename_1] ,
[s].[STUD_Forename_2] ,
[s].[STUD_Known_As] ,
[s].[STUD_DOB] ,
[s].[STUD_Gender] ,
[s].[STUD_Gender_Identity] ,
[s].[STUD_Title] ,
[s].[STUD_Deceased] ,
[s].[STUD_Date_of_Death] ,
[s].[STUD_ISN] ,
[s].[STuD_IS_Staff_ISN]
);
END
It could probably be made more efficient but, considering it's going to part of a nightly task and run when people aren't using the system, it handles ~35000 records in ~2 seconds so it's "efficient enough".