43. Batch Operations & Scheduled Jobs -- Automation

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Batch processing patterns
• SQL Server Agent job creation
• Scheduled tasks and monitoring
• ETL (Extract, Transform, Load)
• Bulk insert and bulk update
• Performance tuning for bulk ops
• Error handling in batch jobs
• Alerting and notifications

Schools run automated processes nightly: enrollment batches, fee collection, attendance generation. This article teaches job automation patterns.

Scenario 1: Scheduled Daily Attendance Generation

Auto-generate attendance records:

-- Create procedure to generate attendance
CREATE PROCEDURE sp_GenerateDailyAttendance
    @SchoolId INT,
    @AttendanceDate DATE = NULL
AS
BEGIN
    SET @AttendanceDate = ISNULL(@AttendanceDate, CAST(GETDATE() AS DATE));
    
    BEGIN TRY
        -- Step 1: Get all active classes and subjects
        WITH AttendanceData AS (
            SELECT DISTINCT
                s.Id AS StudentId,
                sub.Id AS SubjectId,
                @AttendanceDate AS AttendanceDate,
                0 AS IsPresent  -- Default: absent (to be marked later)
            FROM Student s
            INNER JOIN Subject sub ON s.ClassName = sub.ClassName
            WHERE s.SchoolId = @SchoolId
            AND s.Status = 'Active'
            AND NOT EXISTS (
                SELECT 1 FROM Attendance a
                WHERE a.StudentId = s.Id
                AND a.SubjectId = sub.Id
                AND CAST(a.Date AS DATE) = @AttendanceDate
            )
        )
        
        -- Step 2: Batch insert in chunks
        INSERT INTO Attendance (SchoolId, StudentId, SubjectId, Date, IsPresent, MarkedBy)
        SELECT @SchoolId, StudentId, SubjectId, AttendanceDate, IsPresent, 'SYSTEM'
        FROM AttendanceData;
        
        -- Step 3: Log operation
        INSERT INTO AuditLog (SchoolId, Action, EntityName, NewValues, ChangedBy, ChangedAt)
        VALUES (
            @SchoolId, 
            'GenerateAttendance', 
            'Attendance',
            CONCAT('Date: ', @AttendanceDate, ', Records: ', @@ROWCOUNT),
            'SYSTEM',
            GETDATE()
        );
    END TRY
    BEGIN CATCH
        INSERT INTO AuditLog (SchoolId, Action, EntityName, NewValues, ChangedBy, ChangedAt)
        VALUES (@SchoolId, 'GenerateAttendanceError', 'Attendance', ERROR_MESSAGE(), 'SYSTEM', GETDATE());
        THROW;
    END CATCH
END;

-- Test procedure
EXEC sp_GenerateDailyAttendance @SchoolId = 1, @AttendanceDate = '2026-05-24';

Attendance: Generate for all active students daily at 7 AM.

Scenario 2: SQL Server Agent Job Creation

Schedule procedure as automated job:

-- Create job category
EXEC msdb.dbo.sp_add_category
    @class = 'JOB',
    @type = 'LOCAL',
    @name = 'School Operations';

-- Create job
EXEC msdb.dbo.sp_add_job
    @job_name = 'DailyAttendanceGeneration',
    @enabled = 1,
    @description = 'Generate daily attendance records at 7 AM',
    @category_name = 'School Operations',
    @owner_login_name = 'sa';

-- Add job step (procedure execution)
EXEC msdb.dbo.sp_add_jobstep
    @job_name = 'DailyAttendanceGeneration',
    @step_name = 'GenerateAttendance',
    @subsystem = 'TSQL',
    @command = 'EXEC sp_GenerateDailyAttendance @SchoolId = 1;',
    @database_name = 'SchoolDB',
    @on_success_action = 1,  -- Quit with success
    @on_failure_action = 2;  -- Quit with error

-- Add schedule (every day at 7:00 AM)
EXEC msdb.dbo.sp_add_schedule
    @schedule_name = 'DailyAt7AM',
    @freq_type = 4,  -- Daily
    @freq_interval = 1,
    @active_start_time = 070000;  -- 07:00:00

-- Attach schedule to job
EXEC msdb.dbo.sp_attach_schedule
    @job_name = 'DailyAttendanceGeneration',
    @schedule_name = 'DailyAt7AM';

-- View job details
SELECT 
    j.name AS JobName,
    js.step_name,
    js.command,
    s.name AS ScheduleName
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.sysjobschedules jsc ON j.job_id = jsc.job_id
LEFT JOIN msdb.dbo.sysschedules s ON jsc.schedule_id = s.schedule_id
WHERE j.name = 'DailyAttendanceGeneration';

Job: Execute procedure daily at 7 AM. Logs completion/failure.

Scenario 3: Monthly Fee Processing Batch

Process all pending fees:

CREATE PROCEDURE sp_ProcessMonthlyFees
    @SchoolId INT,
    @ProcessingMonth DATE = NULL
AS
BEGIN
    SET @ProcessingMonth = ISNULL(@ProcessingMonth, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));
    
    DECLARE @ProcessedCount INT = 0;
    DECLARE @ErrorCount INT = 0;
    DECLARE @BatchSize INT = 100;
    DECLARE @Offset INT = 0;
    
    BEGIN TRY
        -- Create temp table for processing
        CREATE TABLE #PendingFees (
            FeeAccountId INT,
            StudentId INT,
            StudentName NVARCHAR(100),
            TotalFees DECIMAL(10,2),
            PaidAmount DECIMAL(10,2),
            DueAmount DECIMAL(10,2)
        );
        
        -- Populate pending fees
        INSERT INTO #PendingFees
        SELECT 
            fa.Id,
            fa.StudentId,
            s.Name,
            fa.TotalFees,
            fa.PaidAmount,
            (fa.TotalFees - fa.PaidAmount) AS DueAmount
        FROM FeeAccount fa
        INNER JOIN Student s ON fa.StudentId = s.Id
        WHERE fa.SchoolId = @SchoolId
        AND fa.Status IN ('Pending', 'Partial')
        AND DATEDIFF(MONTH, fa.DueDate, GETDATE()) >= 0;  -- Overdue
        
        -- Process in batches
        WHILE EXISTS (SELECT 1 FROM #PendingFees WHERE FeeAccountId > 0)
        BEGIN
            BEGIN TRANSACTION;
            BEGIN TRY
                -- Process batch of 100
                DECLARE @FeeAccountId INT;
                DECLARE @StudentId INT;
                DECLARE @StudentName NVARCHAR(100);
                DECLARE @DueAmount DECIMAL(10,2);
                
                DECLARE fee_cursor CURSOR FOR
                    SELECT TOP (@BatchSize) FeeAccountId, StudentId, StudentName, DueAmount
                    FROM #PendingFees
                    ORDER BY FeeAccountId;
                
                OPEN fee_cursor;
                
                FETCH NEXT FROM fee_cursor INTO @FeeAccountId, @StudentId, @StudentName, @DueAmount;
                
                WHILE @@FETCH_STATUS = 0
                BEGIN
                    -- Send reminder (update required flag)
                    UPDATE FeeAccount
                    SET Status = 'Overdue'
                    WHERE Id = @FeeAccountId;
                    
                    -- Log action
                    INSERT INTO AuditLog (SchoolId, Action, EntityName, EntityId, NewValues, ChangedBy, ChangedAt)
                    VALUES (@SchoolId, 'FeeReminder', 'FeeAccount', @FeeAccountId, 
                            CONCAT('Student: ', @StudentName, ', Due: ', @DueAmount), 'SYSTEM', GETDATE());
                    
                    SET @ProcessedCount = @ProcessedCount + 1;
                    
                    DELETE FROM #PendingFees WHERE FeeAccountId = @FeeAccountId;
                    
                    FETCH NEXT FROM fee_cursor INTO @FeeAccountId, @StudentId, @StudentName, @DueAmount;
                END;
                
                CLOSE fee_cursor;
                DEALLOCATE fee_cursor;
                
                COMMIT TRANSACTION;
            END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION;
                SET @ErrorCount = @ErrorCount + 1;
                INSERT INTO AuditLog (SchoolId, Action, EntityName, NewValues, ChangedBy, ChangedAt)
                VALUES (@SchoolId, 'FeeBatchError', 'FeeAccount', ERROR_MESSAGE(), 'SYSTEM', GETDATE());
            END CATCH
            
            -- Allow other queries
            WAITFOR DELAY '00:00:02';
        END;
        
        CLOSE fee_cursor;
        DEALLOCATE fee_cursor;
        DROP TABLE #PendingFees;
        
        -- Log summary
        INSERT INTO AuditLog (SchoolId, Action, EntityName, NewValues, ChangedBy, ChangedAt)
        VALUES (@SchoolId, 'MonthlyFeeProcessing', 'FeeAccount',
                CONCAT('Processed: ', @ProcessedCount, ', Errors: ', @ErrorCount),
                'SYSTEM', GETDATE());
    END TRY
    BEGIN CATCH
        IF OBJECT_ID('tempdb..#PendingFees') IS NOT NULL
            DROP TABLE #PendingFees;
        THROW;
    END CATCH
END;

-- Schedule monthly
EXEC msdb.dbo.sp_add_schedule
    @schedule_name = 'MonthlyFeeBatch',
    @freq_type = 16,  -- Monthly
    @freq_interval = 1,  -- First day
    @active_start_time = 020000;  -- 2 AM

Batch: Process 100 records -> commit -> wait -> next. Prevents lock escalation.

Scenario 4: Bulk Student Import from CSV

Load students in bulk:

-- Table for staging
CREATE TABLE Student_Staging (
    RowNum INT,
    Name NVARCHAR(100),
    RollNumber NVARCHAR(20),
    ClassName NVARCHAR(10),
    DateOfBirth DATE,
    ParentName NVARCHAR(100),
    ParentPhone NVARCHAR(20),
    Status NVARCHAR(20),
    ImportedAt DATETIME DEFAULT GETDATE()
);

-- Bulk insert from file
BULK INSERT Student_Staging
FROM 'D:\imports\students.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,  -- Skip header
    TABLOCK,  -- Minimal locking
    MAXERRORS = 10
);

-- Validate staging
CREATE PROCEDURE sp_ValidateStudentImport
AS
BEGIN
    -- Check for duplicates
    SELECT RollNumber, ClassName, COUNT(*)
    FROM Student_Staging
    GROUP BY RollNumber, ClassName
    HAVING COUNT(*) > 1;
    
    -- Check for invalid dates
    SELECT * FROM Student_Staging
    WHERE DateOfBirth IS NULL
    OR DATEDIFF(YEAR, DateOfBirth, GETDATE()) < 5;
    
    -- Check for missing fields
    SELECT * FROM Student_Staging
    WHERE Name IS NULL
    OR RollNumber IS NULL
    OR ClassName IS NULL;
END;

-- Run validation
EXEC sp_ValidateStudentImport;

-- If valid, move to production
CREATE PROCEDURE sp_ConfirmStudentImport
    @SchoolId INT
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO Student (SchoolId, Name, RollNumber, ClassName, DateOfBirth, ParentName, ParentPhone, Status)
        SELECT @SchoolId, Name, RollNumber, ClassName, DateOfBirth, ParentName, ParentPhone, Status
        FROM Student_Staging;
        
        -- Log
        INSERT INTO AuditLog (SchoolId, Action, EntityName, NewValues, ChangedBy, ChangedAt)
        VALUES (@SchoolId, 'BulkImport', 'Student', CONCAT('Imported: ', @@ROWCOUNT, ' students'), 'SYSTEM', GETDATE());
        
        -- Clear staging
        DELETE FROM Student_Staging;
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

Import: Stage -> Validate -> Confirm. Minimal locking with TABLOCK.

Scenario 5: Job Execution Monitoring

Monitor job health:

-- View job execution history
CREATE VIEW vw_JobExecutionHistory AS
SELECT 
    j.name AS JobName,
    js.step_name AS StepName,
    CASE 
        WHEN jh.run_status = 0 THEN 'Failed'
        WHEN jh.run_status = 1 THEN 'Success'
        WHEN jh.run_status = 2 THEN 'Retry'
        WHEN jh.run_status = 3 THEN 'Cancelled'
        ELSE 'Unknown'
    END AS Status,
    CONCAT(
        SUBSTRING(CAST(jh.run_date AS VARCHAR(8)), 1, 4), '-',
        SUBSTRING(CAST(jh.run_date AS VARCHAR(8)), 5, 2), '-',
        SUBSTRING(CAST(jh.run_date AS VARCHAR(8)), 7, 2), ' ',
        SUBSTRING(CAST(jh.run_time AS VARCHAR(6)), 1, 2), ':',
        SUBSTRING(CAST(jh.run_time AS VARCHAR(6)), 3, 2), ':',
        SUBSTRING(CAST(jh.run_time AS VARCHAR(6)), 5, 2)
    ) AS ExecutionTime,
    jh.run_duration AS DurationSeconds,
    jh.message AS Message
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
LEFT JOIN msdb.dbo.sysjobhistory jh ON js.job_id = jh.job_id AND js.step_id = jh.step_id
WHERE jh.run_date IS NOT NULL
ORDER BY jh.run_date DESC, jh.run_time DESC;

-- Query recent executions
SELECT TOP 20 * FROM vw_JobExecutionHistory;

-- Alert on job failure
CREATE PROCEDURE sp_AlertJobFailures
    @JobName NVARCHAR(100)
AS
BEGIN
    DECLARE @FailureCount INT;
    
    SELECT @FailureCount = COUNT(*)
    FROM msdb.dbo.sysjobhistory jh
    INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
    WHERE j.name = @JobName
    AND jh.run_status = 0
    AND DATEDIFF(HOUR, GETDATE(), CONVERT(DATETIME, CAST(jh.run_date AS CHAR(8)))) <= 24;
    
    IF @FailureCount > 0
    BEGIN
        -- Send alert (implementation varies)
        EXEC msdb.dbo.sp_send_dbmail
            @subject = CONCAT('SQL Job Failed: ', @JobName),
            @body = CONCAT('Job ', @JobName, ' failed ', @FailureCount, ' times in last 24 hours'),
            @profile_name = 'SchoolProfileAgent';
    END;
END;

Monitor: Job status, duration, failures. Alert on errors.

Scenario 6: Year-End Archival Batch

Archive completed academic year:

CREATE PROCEDURE sp_ArchiveAcademicYear
    @SchoolId INT,
    @AcademicYear NVARCHAR(9)
AS
BEGIN
    BEGIN TRY
        -- Create archive tables if not exist
        IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Student_Archive')
        BEGIN
            SELECT * INTO Student_Archive FROM Student WHERE 1 = 0;
        END;
        
        IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ExamResult_Archive')
        BEGIN
            SELECT * INTO ExamResult_Archive FROM ExamResult WHERE 1 = 0;
        END;
        
        -- Archive exam results
        INSERT INTO ExamResult_Archive
        SELECT *
        FROM ExamResult er
        WHERE er.SchoolId = @SchoolId
        AND EXISTS (
            SELECT 1 FROM Exam e
            WHERE e.Id = er.ExamId
            AND YEAR(e.ExamDate) = CAST(@AcademicYear AS INT)
        );
        
        -- Mark archived records
        UPDATE ExamResult
        SET IsArchived = 1
        WHERE SchoolId = @SchoolId
        AND EXISTS (
            SELECT 1 FROM Exam e
            WHERE e.Id = ExamId
            AND YEAR(e.ExamDate) = CAST(@AcademicYear AS INT)
        );
        
        -- Archive fee records
        INSERT INTO FeeAccount_Archive
        SELECT *
        FROM FeeAccount
        WHERE SchoolId = @SchoolId
        AND AcademicYear = @AcademicYear;
        
        -- Log archival
        INSERT INTO AuditLog (SchoolId, Action, EntityName, NewValues, ChangedBy, ChangedAt)
        VALUES (
            @SchoolId,
            'YearEndArchive',
            'AllData',
            CONCAT('Academic Year: ', @AcademicYear, ', Archived'),
            'SYSTEM',
            GETDATE()
        );
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END;

-- Schedule for June 30 each year
EXEC msdb.dbo.sp_add_schedule
    @schedule_name = 'YearEndArchive',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_recurrence_factor = 12,
    @active_start_date = 20260630;

Archive: Old exams, fees per academic year. Reduce production DB size.

---

🎯 Q1: How do I generate daily attendance automatically?

Procedure:

1. Get all active students + subjects
2. Insert missing attendance records (default: absent)
3. Run daily via SQL Agent job at 7 AM

UPDATE attendance later when marked present.

🎯 Q2: How do I create SQL Server Agent jobs?

sp_add_job, sp_add_jobstep, sp_add_schedule, sp_attach_schedule
Job -> Step (TSQL command) -> Schedule (daily/monthly/etc)

Enable Agent service. View results in job history.

🎯 Q3: Should I batch large operations?

Yes. Process 100-1000 records per batch:

• Commit per batch
• Small delay between batches
• Reduces lock time
• Other queries can run

Prevents blocking whole table.

🎯 Q4: How do I bulk import from CSV?

Staging table -> BULK INSERT -> Validate -> Confirm

BULK INSERT: Fast, minimal locking (TABLOCK). Validate: Check duplicates, required fields, date ranges. Confirm: Move to production, clear staging.

🎯 Q5: How do I monitor job executions?

SELECT * FROM msdb.dbo.sysjobhistory
WHERE run_status = 0  -- Failed
AND run_date > CAST(GETDATE() AS INT)

View: Job status, duration, errors. Alert: Send email on failure.

🎯 Q6: When should I archive old data?

Archive if:

• Data queried rarely
• Compliance requires retention
• DB size too large
• Year-end cleanup

Move to archive table, mark as archived, free space.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Batch operations and scheduled jobs in SQL Server. Try these prompts:

• "Show daily attendance generation scheduled job"
• "How do I create SQL Server Agent jobs with schedules?"
• "How do I process fees in batches without locking?"
• "Show bulk import from CSV with validation"
• "How do I monitor job execution and alert on failure?"
• "Quiz me: 5 questions about batch processing, jobs, automation"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

SQL Server Section Links

Use these links to continue the full Backend > SQL Server topic from the top menu:

• SQL Server Tutorial
• SQL Server Joins
• Stored Procedures and Functions
• SQL Server Performance Optimization
• SQL Server with ASP.NET Core Web API
• ASP.NET Core Web API
• Entity Framework Core
• SQL Server Interview Questions

Target search terms for this lesson: sql server agent jobs, batch processing sql server, scheduled jobs sql server, sql automation jobs.

---

Next Article

44. Schema Evolution & Maintenance ->