Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =============================================
-- Description: Backfil FK need for raw ConsultFactor table
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSyncRawFKConsultFactors]
AS
DECLARE @TABLE_NAME varchar(100); --make constant value for use thoughout
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--Set @TABLE_NAME value
SET @TABLE_NAME = 'zraw._conConsultFactor';
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END
--create any needed indexes to speed up cleaning
--only indexes applied are to the raw tables
--no more than 5 indexes in the cleaner
CREATE NONCLUSTERED INDEX IX_conConsultFactor_Sta3n ON zraw._conConsultFactor
(
Sta3n
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON StagingIndex;
CREATE NONCLUSTERED INDEX IX_conConsultFactor_ConsultIEN ON zraw._conConsultFactor
(
ConsultIEN
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON StagingIndex;
CREATE NONCLUSTERED INDEX IX_conConsultFactor_ConsultActivityIEN ON zraw._conConsultFactor
(
ConsultActivityIEN
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON StagingIndex;
CREATE NONCLUSTERED INDEX IX_conConsultFactor_EnteredByStaffIEN ON zraw._conConsultFactor
(
EnteredByStaffIEN
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON StagingIndex;
--set recordstatusId based on OpCode
UPDATE zraw._conConsultFactor SET
recordstatusId = RecordStatuses.recordstatusId
FROM RecordStatuses
INNER JOIN zraw._conConsultFactor ON RecordStatuses.RecordStatusCode = zraw._conConsultFactor.OpCode
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);
--set StationID based on STA3N
UPDATE zraw._conConsultFactor SET
StationID = Stations.StationID
FROM Stations
INNER JOIN zraw._conConsultFactor ON Stations.Station6A = zraw._conConsultFactor.Sta3n
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID)
and Stations.ParentStation = 1;
--set ConsultID based on ConsultSID
UPDATE zraw._conConsultFactor SET
ConsultID = Consults.ConsultID
FROM Consults
INNER JOIN zraw._conConsultFactor ON Consults.ConsultID = zraw._conConsultFactor.ConsultID
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);
--set ConsultActivityID based on ConsultActivitySID
UPDATE zraw._conConsultFactor SET
ConsultActivityID = ConsultActivities.ConsultActivityID
FROM ConsultActivities
INNER JOIN zraw._conConsultFactor ON ConsultActivities.ConsultActivityID = zraw._conConsultFactor.ConsultActivityID
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);
--set SConsultActivityCommentID based on ConsultActivitySID
UPDATE zraw._conConsultFactor SET
SConsultActivityCommentID = SConsultActivityComments.SConsultActivityCommentID
FROM SConsultActivityComments
INNER JOIN zraw._conConsultFactor ON SConsultActivityComments.SConsultActivityCommentID = zraw._conConsultFactor.SConsultActivityCommentID
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);
--set ConsultFactorTypeID based on ConsultActivitySID
UPDATE zraw._conConsultFactor SET
ConsultFactorTypeID = ConsultFactorTypes.ConsultFactorTypeID
FROM ConsultFactorTypes
INNER JOIN zraw._conConsultFactor ON ConsultFactorTypes.ConsultFactorTypeID = zraw._conConsultFactor.ConsultFactorTypeID
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);
--set PatientID based on PatientSID
UPDATE zraw._conConsultFactor SET
PatientID = Patients.PatientID
FROM Patients
INNER JOIN zraw._conConsultFactor ON Patients.PatientID = zraw._conConsultFactor.PatientID
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);
--set StaffUserID based on StaffSID
UPDATE zraw._conConsultFactor SET
StaffUserID = StaffUsers.StaffUserID
FROM StaffUsers
INNER JOIN zraw._conConsultFactor ON StaffUsers.StaffUserID = zraw._conConsultFactor.StaffUserID
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE zraw._conConsultFactor.BatchLogID = #batchTableConsultFactors.BatchLogID);
/**
--StationID smallint NOT NULL, --EPRS assigned (FK), reference Stations
--ConsultID bigint NOT NULL, --EPRS assigned (FK), reference consults
--ConsultActivityID bigint NOT NULL, --EPRS assigned (FK), reference consultActivities
--SConsultActivityCommentID bigint NOT NULL, --EPRS assigned (FK), reference SConsultActivityComments
--ConsultFactorTypeID smallint NULL, --EPRS assigned (FK), reference ConsultFactors
--PatientID bigint NULL, --EPRS assigned (FK), reference Patients
--StaffUserID bigint NULL, --EPRS assigned (FK), reference StaffUsers
--recordstatusId tinyint NULL, --EPRS assigned (FK), references RecordStatuses
**/
--drop any raw index objects
-- NEXT STEP
DROP INDEX IX_conConsultFactor_Sta3n ON zraw._conConsultFactor;
DROP INDEX IX_conConsultFactor_ConsultIEN ON zraw._conConsultFactor;
DROP INDEX IX_conConsultFactor_ConsultActivityIEN ON zraw._conConsultFactor;
DROP INDEX IX_conConsultFactor_EnteredByStaffIEN ON zraw._conConsultFactor;
--drop the #batchTableConsultFactors if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END
END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableConsultFactors
WHERE BatchLogs.BatchLogID = #batchTableConsultFactors.BatchLogID);
--drop the #batchTableConsultFactors if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableConsultFactors;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableConsultFactors') IS NOT NULL
BEGIN
DROP TABLE #batchTableConsultFactors;
END
PRINT 'There was an error cleaning raw Consult Factor data' + CHAR(13);
THROW;
END CATCH
END