Consider SSD re-work to declare schema_name, rather than existing search-replace by LAs #54
Description
Right now LAs do a search an replace on 'ssd_development.' - to enable them to insert their own schema name if need. IT's a step we could avoid if we defaulted to an empty string, but this would require some further, but basic work on the SSD script to enable a DECLARE'd var of schema_name and changes around each table create and insert.
e.g.
DECLARE @ssd_schema sysname = N''
IF OBJECT_ID(@ssd_prefix + 'ssd_linked_identifiers', 'U') IS NULL BEGIN DECLARE @sql_create nvarchar(max) = N' CREATE TABLE ' + @ssd_prefix + 'ssd_linked_identifiers ( link_table_id NVARCHAR(48) DEFAULT NEWID() PRIMARY KEY, link_person_id NVARCHAR(48), link_identifier_type NVARCHAR(100), link_identifier_value NVARCHAR(100), link_valid_from_date DATETIME, link_valid_to_date DATETIME );'; EXEC sp_executesql @sql_create; END;
and
`DECLARE @sql_ins1 nvarchar(max) = N'
INSERT INTO ' + @ssd_prefix + 'ssd_linked_identifiers (
link_person_id,
link_identifier_type,
link_identifier_value,
link_valid_from_date,
link_valid_to_date
)
SELECT
csp.dim_person_id AS link_person_id,
''Former Unique Pupil Number'' AS link_identifier_type,
''SSD_PH'' AS link_identifier_value,
NULL AS link_valid_from_date,
NULL AS link_valid_to_date
FROM
HDM.Child_Social.DIM_PERSON csp
WHERE
csp.former_upn IS NOT NULL
AND EXISTS (
SELECT 1
FROM ' + @ssd_prefix + 'ssd_person p
WHERE p.pers_person_id = csp.dim_person_id
);';
EXEC sp_executesql @sql_ins1;`