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: Cleans the TIU Documents
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoCleanTIUDocuments]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
--TODO: Do we need to bother with batching this definition?

--LTRIM/RTRIM any string-based fields
--TODO: need to finish cleaning script
UPDATE zraw._TIUTIUDocuments SET
TIUDocumentDefinition = NULLIF(LTRIM(RTRIM(TIUDocumentDefinition)), '');


--Backfill the StationID; We know this is in the wrong spot, stationID needed now for other table syncronizers
UPDATE zraw._TIUTIUDocuments SET
StationID = Stations.StationID
FROM Stations
INNER JOIN zraw._TIUTIUDocuments ON zraw._TIUTIUDocuments.Sta3n = Stations.Station3N
--INNER JOIN @batchTable bt ON CON.BATCHLOGID = bt.BatchLogID
WHERE ParentStation = 1;


--create any needed indexes to speed up cleaning
/*
CREATE NONCLUSTERED INDEX IX_dimRequestService_ServiceName ON zraw._dimRequestService
(
ServiceName
) 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 --don't need, less than 10,000 rows
)
ON StagingIndex;
*/



--Clean up index objects
--DROP INDEX IX_dimRequestService_ServiceName
-- ON zraw._dimRequestService;

END TRY
BEGIN CATCH
PRINT 'There was an error cleaning Request Service raw data' + CHAR(13);
THROW;
END CATCH
END