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: Synchs the TIUDocumentDefinitions
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSyncTIUDocumentDefinitions]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--clean TIUDocumentDefinitions
--Done as part of the TIUDocuments cleaner
--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableTIUDocumentDefinitions;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableTIUDocumentDefinitions') IS NOT NULL
BEGIN
DROP TABLE #batchTableTIUDocumentDefinitions;
END
--need to restrict the records we modified to only those available for internalprocessing
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableTIUDocumentDefinitions
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', 'zraw._TIUDocumentDefinitions');
--Update existing records where necessary
UPDATE TIUDocumentDefinitions SET
TIUDocumentDefinitionName = rawTx.TIUDocumentDefinitionID
, CDWTIUDocumentDefinitionSysNumber = rawTx.TIUDocumentDefinitionSID
FROM zraw._TIUTIUDocuments rawTx
INNER JOIN #batchTableTIUDocumentDefinitions bt ON rawTx.BatchLogID = bt.BatchLogID
INNER JOIN TIUDocumentDefinitions ON rawTx.StationID = TIUDocumentDefinitions.StationID
AND rawTx.TIUDocumentDefinitionIEN = TIUDocumentDefinitions.TIUDocumentDefinitionIEN
WHERE rawTx.StationID IS NOT NULL
AND rawTx.TIUDocumentDefinitionIEN IS NOT NULL;
--insert new records found
INSERT INTO TIUDocumentDefinitions WITH (TABLOCK)
(
StationID
,TIUDocumentDefinitionName
,CDWTIUDocumentDefinitionSysNumber
,TIUDocumentDefinitionIEN
)
SELECT DISTINCT
StationID
, TIUDocumentDefinition
, TIUDocumentDefinitionSID
, TIUDocumentDefinitionIEN
FROM zraw._TIUTIUDocuments rawTx
INNER JOIN #batchTableTIUDocumentDefinitions bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE NOT EXISTS (SELECT 1 FROM TIUDocumentDefinitions
WHERE rawTx.StationID = TIUDocumentDefinitions.StationID
AND rawTx.TIUDocumentDefinitionIEN = TIUDocumentDefinitions.TIUDocumentDefinitionIEN)
AND rawTx.TIUDocumentDefinition IS NOT NULL
AND rawTx.StationID IS NOT NULL
AND rawTx.TIUDocumentDefinitionIEN IS NOT NULL;
--final cleanup...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableTIUDocumentDefinitions;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableTIUDocumentDefinitions') IS NOT NULL
BEGIN
DROP TABLE #batchTableTIUDocumentDefinitions;
END
END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableTIUDocumentDefinitions
WHERE BatchLogs.BatchLogID = #batchTableTIUDocumentDefinitions.BatchLogID);
--drop any raw index objects
--drop the ##batchTableStopCodes if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableTIUDocumentDefinitions;
--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableTIUDocumentDefinitions') IS NOT NULL
BEGIN
DROP TABLE #batchTableTIUDocumentDefinitions;
END
PRINT 'There was an error sychronizing TIU Document defintion data' + CHAR(13);
THROW;
END CATCH
END
GO