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: Synchronizes the stations with what might come in from CDW Dim.Sta3n
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.[uspDoSyncStations]
@canCleanStagingTables bit
AS
DECLARE @batchLogID bigint,
@resultOk bit,
@TABLE_NAME varchar(100);
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._dimSta3n';

--drop the #batchTable if it exists...
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStations;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStations') IS NOT NULL
BEGIN
DROP TABLE #batchTableStations;
END

--If there are any records in BatchLogs for dimSta3n that are marked EXTCOMPLETE
--update the status to INTPROCESSING;
UPDATE dbo.BatchLogs SET
LoadStatus = 'INTPROCESSING'
WHERE LoadStatus = 'EXTCOMPLETE'
AND TableName = @TABLE_NAME;

--populate #batchTable with BatchLogIDs that are ok to process per INTPROCESSING
SELECT BatchLogID, TableName, BatchDate
INTO #batchTableStations
FROM dbo.ufnGetBatchLogsForTableProcessing ('INTPROCESSING', @TABLE_NAME);

--Clean up SiteCodes (don't bother with a separate cleaning script for Stations, this is the only script needed
--LTRIM/RTRIM any string-based fields and Null out any empty strings
UPDATE zraw._dimSta3n SET
SiteCode = NULLIF(LTRIM(RTRIM(SiteCode)), '')
WHERE EXISTS (SELECT 1 FROM #batchTableStations
WHERE #batchTableStations.BatchLogID = zraw._dimSta3n.BatchLogID);

--Updates/inserts are really simple for Stations,
--EPRS stations list is more comprehensive than tables found elsewhere
--update VisnIDs if needed
UPDATE Stations SET
VisnID = rawTx.VISN
FROM zraw._dimSta3n rawTx
INNER JOIN Stations ON rawTx.Sta3n = Stations.Station3N
INNER JOIN #batchTableStations bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE rawTx.VISN Is Not Null;

UPDATE Stations SET
RegionID = rawTx.Region
FROM zraw._dimSta3n rawTx
INNER JOIN Stations ON rawTx.Sta3n = Stations.Station3N
INNER JOIN #batchTableStations bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE rawTx.Region IS NOT NULL;

--update moniker from SiteCodes
UPDATE Stations SET
Moniker = rawTx.SiteCode
FROM zraw._dimSta3n rawTX
INNER JOIN Stations ON rawTx.Sta3n = Stations.Station3N
INNER JOIN #batchTableStations bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE SiteCode IS NOT NULL
AND ParentStation = 1;

--update latitude, longitude, address info
UPDATE Stations SET
Moniker = rawTx.SiteCode
FROM zraw._dimSta3n rawTX
INNER JOIN Stations ON rawTx.Sta3n = Stations.Station3N
INNER JOIN #batchTableStations bt ON rawTx.BatchLogID = bt.BatchLogID
WHERE SiteCode IS NOT NULL
AND ParentStation = 1;

--Log the records touched and delete records from raw
--HOWEVER, only do this if the incoming parameter @canCleanStagingTables = true (1)
SELECT @batchLogID = MIN(BatchLogID)
FROM #batchTableStations;

WHILE @batchLogID <> 0
BEGIN
--update synch logs for how many fact records inserted/updated
--note that the first param (@tableName) will need to include the schema name, if not dbo
--We aren't tracking batchlog identifiers in Stations table, so this snippet won't work
--EXEC uspSetSynchLogs 'Stations', @batchLogID

IF @canCleanStagingTables = 1
BEGIN
--delete the raws
EXEC uspDoDeleteRaw @batchLogID, @TABLE_NAME, @resultOk OUTPUT;
DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS;

END
--get the next key in the @batchDateTable
SELECT @batchLogID = MIN(BatchLogID)
FROM #batchTableStations
WHERE BatchLogID > @batchLogID;
END

--update status on batch table records
UPDATE BatchLogs SET
LoadStatus = 'INTCOMPLETE'
, LoadCompleteDate = GETDATE()
WHERE EXISTS (SELECT 1 FROM #batchTableStations
WHERE BatchLogs.BatchLogID = #batchTableStations.BatchLogID);

--drop batch table
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStations;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStations') IS NOT NULL
BEGIN
DROP TABLE #batchTableStations;
END

END TRY
BEGIN CATCH
--identify that records in the batch have errored
UPDATE BatchLogs SET
LoadStatus = 'INTERROR'
WHERE EXISTS (SELECT 1 FROM #batchTableStations
WHERE BatchLogs.BatchLogID = #batchTableStations.BatchLogID);

--drop batch table
--SQL 2016 script-- DROP TABLE IF EXISTS #batchTableStations;

--SQL 2014 script--
--Drop temp table
IF OBJECT_ID(N'tempdb..#batchTableStations') IS NOT NULL
BEGIN
DROP TABLE #batchTableStations;
END

PRINT 'There was an error synchronizing raw Station data' + CHAR(13);
THROW;
END CATCH
END