USE STG_RM_Connector
GO

DECLARE @tblAttCode sysname, @tblAttSess sysname, @tblStaffServ sysname, @tblStuSer sysname
DECLARE @rowCntAttCode int,  @rowCntAttSess int, @rowCntStaffServ int, @rowCntStuSer int

SET @tblAttSess = 'Attendance Session Pupil Data'
SET @tblAttCode = 'Attendace Code Service Attendace Codes'
SET @tblStaffServ = 'Staff Service Staff Members'
SET @tblStuSer = 'Student Service Pupil Data'

SELECT @rowCntAttCode = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttCode
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

SELECT @rowCntAttSess = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblAttSess
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

SELECT @rowCntStaffServ = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblStaffServ
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

SELECT @rowCntStuSer = SUM(PART.rows)
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @tblStuSer
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

IF @rowCntAttCode = 0 AND @rowCntAttSess = 0 AND @rowCntStaffServ = 0 AND @rowCntStuSer = 0
BEGIN
 RAISERROR ('Failed to pull data for all tables', 16, 1)
END
ELSE IF @rowCntAttCode = 0 OR @rowCntAttSess = 0 OR @rowCntStaffServ = 0 OR @rowCntStuSer = 0
    BEGIN
        RAISERROR ('Failed to pull data for one of the RM staging tables', 10, 1)
    END
ELSE
BEGIN
  PRINT 'Successfully pulled data for all RM staging tables'
END
GO