SQL DML Scripting for Automation

• Always name columns in production INSERT statements.
• Preview an UPDATE or DELETE predicate with SELECT.
• Use primary keys or approved business keys to target rows precisely.
• Pass SCADA values as parameters rather than concatenating SQL strings.

1. What Is DML?

DML means Data Manipulation Language. It retrieves and changes the rows stored in tables or exposed through eligible views.

Some textbooks call SELECT DQL (Data Query Language), while SQL Server documentation commonly discusses it alongside data manipulation. The useful engineering distinction is simple: DML works with rows; DDL works with structure.

2. DML vs DDL, DCL and Transactions

3. Inspect tblEvent Before Changing Data

Never assume the production table matches a training screenshot. Confirm the database, object, columns and a small sample first.

USE [SQF_DB];
GO

SELECT DB_NAME() AS [CurrentDatabase];

SELECT
    c.[column_id], c.[name] AS [ColumnName],
    t.[name] AS [DataType], c.[max_length],
    c.[precision], c.[scale], c.[is_nullable]
FROM sys.columns AS c
JOIN sys.types AS t ON t.[user_type_id] = c.[user_type_id]
WHERE c.[object_id] = OBJECT_ID(N'dbo.tblEvent')
ORDER BY c.[column_id];

SELECT TOP (10)
    [ID], [DT], [SQF_No], [ChargeNo],
    [Event_From], [Event_To], [Temp_Set], [Temp_Act], [Fan_Status]
FROM [dbo].[tblEvent]
ORDER BY [DT] DESC, [ID] DESC;

4. SELECT the Columns You Need

SELECT
    [ID], [DT], [SQF_No], [ChargeNo],
    [Temp_Set], [Temp_Act],
    ([Temp_Act] - [Temp_Set]) AS [TempDeviation],
    [Fan_Status]
FROM [dbo].[tblEvent];

Avoid SELECT * in stable SCADA interfaces. Explicit columns document the contract, reduce unnecessary network traffic and prevent a later schema change from silently altering column order or payload size.

An alias such as TempDeviation names the result expression; it does not add a column to the table.

5. Filter Rows with WHERE

DECLARE @EquipmentID int = 2;
DECLARE @FromUtc datetime2(3) = '2026-08-04T06:00:00.000';
DECLARE @ToUtc   datetime2(3) = '2026-08-04T14:00:00.000';

SELECT [ID], [DT], [ChargeNo], [Temp_Set], [Temp_Act]
FROM [dbo].[tblEvent]
WHERE [SQF_No] = @EquipmentID
  AND [DT] >= @FromUtc
  AND [DT] <  @ToUtc
ORDER BY [DT], [ID];

The half-open time range (>= start and < end) avoids double-counting a boundary when adjacent shifts or batches are queried.

6. ORDER BY, TOP and Paging

Without ORDER BY, SQL Server does not promise row order. Add a unique tiebreaker such as ID when timestamps can repeat.

SELECT TOP (20)
    [ID], [DT], [SQF_No], [ChargeNo], [Event_To]
FROM [dbo].[tblEvent]
ORDER BY [DT] DESC, [ID] DESC;

-- Page 2 when each page contains 20 rows.
SELECT [ID], [DT], [SQF_No], [ChargeNo], [Event_To]
FROM [dbo].[tblEvent]
ORDER BY [DT] DESC, [ID] DESC
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

7. Join Event and Equipment Data

SELECT
    e.[ID], e.[DT], e.[ChargeNo],
    q.[EquipmentName],
    e.[Temp_Set], e.[Temp_Act], e.[Fan_Status]
FROM [dbo].[tblEvent] AS e
LEFT JOIN [dbo].[Equipment] AS q
    ON q.[EquipmentID] = e.[SQF_No]
WHERE e.[DT] >= DATEADD(HOUR, -8, SYSUTCDATETIME())
ORDER BY e.[DT] DESC, e.[ID] DESC;

INNER JOIN returns only matching equipment. LEFT JOIN retains every event and returns NULL for missing parent details, which is useful when diagnosing incomplete legacy mappings.

8. Aggregate Process Data with GROUP BY

SELECT
    [SQF_No],
    COUNT_BIG(*) AS [EventCount],
    MIN([DT]) AS [FirstEventUtc],
    MAX([DT]) AS [LastEventUtc],
    AVG(CONVERT(decimal(18,3), [Temp_Act])) AS [AverageTemp],
    MAX([Temp_Act]) AS [MaximumTemp]
FROM [dbo].[tblEvent]
WHERE [DT] >= DATEADD(DAY, -1, SYSUTCDATETIME())
GROUP BY [SQF_No]
ORDER BY [SQF_No];

COUNT(column) ignores NULL; COUNT(*) and COUNT_BIG(*) count rows. Aggregates such as AVG also ignore NULL, so report the valid-sample count when missing measurements matter.

SELECT
    [SQF_No],
    COUNT_BIG(*) AS [AllRows],
    COUNT([Temp_Act]) AS [ValidTemperatureRows],
    SUM(CASE WHEN [Temp_Act] IS NULL THEN 1 ELSE 0 END) AS [MissingTemperatureRows]
FROM [dbo].[tblEvent]
GROUP BY [SQF_No];

9. INSERT One Automation Event

Specify the target columns explicitly. Omit the identity column and let SQL Server generate it.

INSERT INTO [dbo].[tblEvent]
(
    [DT], [SQF_No], [ChargeNo], [Event_From], [Event_To],
    [Temp_Set], [Temp_Act], [Fan_Status]
)
VALUES
(
    SYSUTCDATETIME(), 2, 'CH-2026-0042', 'Heating', 'Soaking',
    950.000, 947.625, 1
);

Explicit column lists survive column reordering and make omitted default/nullable columns intentional. For Unicode columns, prefix literals with N, for example N'भट्ठी 2'.

10. INSERT Multiple Rows

INSERT INTO [dbo].[tblEvent]
    ([DT], [SQF_No], [ChargeNo], [Event_From], [Event_To], [Temp_Set], [Temp_Act], [Fan_Status])
VALUES
    ('2026-08-04T08:00:00.000', 2, 'CH-2026-0042', 'Load',    'Heating', 950.000, 120.500, 1),
    ('2026-08-04T08:30:00.000', 2, 'CH-2026-0042', 'Heating', 'Soaking', 950.000, 948.250, 1),
    ('2026-08-04T09:00:00.000', 2, 'CH-2026-0042', 'Soaking', 'Cooling', 950.000, 930.100, 0);

A multi-row constructor reduces round trips for small batches. For high-volume acquisition, use a staging table plus bulk loading, a table-valued parameter, or a purpose-built ingestion path rather than building one enormous SQL string.

11. Capture Identity and Changed Values with OUTPUT

The OUTPUT clause returns values from the rows affected by DML. It is more direct than running a separate query to find the inserted event.

INSERT INTO [dbo].[tblEvent]
    ([DT], [SQF_No], [ChargeNo], [Event_From], [Event_To], [Temp_Set], [Temp_Act], [Fan_Status])
OUTPUT
    inserted.[ID], inserted.[DT], inserted.[SQF_No], inserted.[ChargeNo]
VALUES
    (SYSUTCDATETIME(), 2, 'CH-2026-0043', 'Load', 'Heating', 925.000, 110.250, 1);

Use inserted for new values and deleted for old values in UPDATE/DELETE output.

12. INSERT...SELECT and Staging Tables

INSERT...SELECT moves a set of rows while keeping the operation inside SQL Server. Match columns by meaning and compatible data type—not by accidental position.

INSERT INTO [dbo].[tblEvent]
    ([DT], [SQF_No], [ChargeNo], [Event_From], [Event_To], [Temp_Set], [Temp_Act], [Fan_Status])
SELECT
    s.[EventTimeUtc], s.[EquipmentID], s.[BatchCode],
    s.[PreviousState], s.[NewState],
    s.[TemperatureSetpoint], s.[TemperatureActual], s.[FanOn]
FROM [dbo].[tblEvent_Stage] AS s
WHERE s.[ValidationStatus] = 'VALID'
  AND NOT EXISTS
  (
      SELECT 1
      FROM [dbo].[tblEvent] AS e
      WHERE e.[SQF_No] = s.[EquipmentID]
        AND e.[DT] = s.[EventTimeUtc]
        AND e.[ChargeNo] = s.[BatchCode]
  );

The NOT EXISTS check reduces duplicate loads, but concurrency still requires an enforced unique key or an appropriate serialized ingestion design.

13. UPDATE Rows Safely

Use the same predicate for preview and modification. Target the primary key whenever possible.

DECLARE @EventID int = 1250;

-- 1. Preview
SELECT [ID], [DT], [SQF_No], [ChargeNo], [Temp_Act]
FROM [dbo].[tblEvent]
WHERE [ID] = @EventID;

-- 2. Update exactly one event
UPDATE [dbo].[tblEvent]
SET [Temp_Act] = 948.875
WHERE [ID] = @EventID;

-- 3. Verify
SELECT [ID], [Temp_Act]
FROM [dbo].[tblEvent]
WHERE [ID] = @EventID;

14. UPDATE Using a Join

A joined update can apply approved reference data to matching rows. Preview the join first and ensure it does not duplicate the target row.

-- Preview the exact key/value pairs.
SELECT e.[ID], e.[SQF_No], e.[Event_To], m.[NormalizedState]
FROM [dbo].[tblEvent] AS e
JOIN [dbo].[StateMap] AS m
    ON m.[LegacyState] = e.[Event_To]
WHERE e.[DT] >= '2026-08-01T00:00:00';

-- Apply the approved mapping.
UPDATE e
SET e.[Event_To] = m.[NormalizedState]
FROM [dbo].[tblEvent] AS e
JOIN [dbo].[StateMap] AS m
    ON m.[LegacyState] = e.[Event_To]
WHERE e.[DT] >= '2026-08-01T00:00:00';

If more than one source row matches the same target, the chosen source value is not a safe business rule. Make the source key unique before running the update.

15. DELETE Only Approved Rows

DELETE removes rows, preserves the table and supports WHERE. Always preview and count the target set.

DECLARE @CutoffUtc datetime2(3) = '2025-08-01T00:00:00.000';

SELECT COUNT_BIG(*) AS [RowsProposedForDeletion]
FROM [dbo].[tblEvent_Stage]
WHERE [DT] < @CutoffUtc;

BEGIN TRANSACTION;

DELETE FROM [dbo].[tblEvent_Stage]
WHERE [DT] < @CutoffUtc;

SELECT @@ROWCOUNT AS [DeletedRows];

-- Use ROLLBACK during rehearsal; COMMIT only after approval and verification.
ROLLBACK TRANSACTION;

16. Capture Updated or Deleted Rows

Capture the exact before/after values in the same operation. This is useful for verification or an application response, but it does not replace a secured, complete audit architecture.

DECLARE @Changed table
(
    [ID] int,
    [OldTemp] decimal(9,3),
    [NewTemp] decimal(9,3),
    [ChangedUtc] datetime2(3)
);

UPDATE [dbo].[tblEvent]
SET [Temp_Act] = 949.125
OUTPUT
    inserted.[ID], deleted.[Temp_Act], inserted.[Temp_Act], SYSUTCDATETIME()
INTO @Changed ([ID], [OldTemp], [NewTemp], [ChangedUtc])
WHERE [ID] = 1250;

SELECT * FROM @Changed;

DELETE FROM [dbo].[tblEvent_Stage]
OUTPUT deleted.[ID], deleted.[DT], deleted.[SQF_No], deleted.[ChargeNo]
WHERE [LoadBatchID] = 9001
  AND [ValidationStatus] = 'REJECTED';

17. Transactions with TRY/CATCH

Use one transaction when several DML statements represent one business unit. Keep it short; open transactions retain locks and log records.

SET XACT_ABORT ON;
DECLARE @PreviousEventID int = 1249;

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO [dbo].[tblEvent]
        ([DT], [SQF_No], [ChargeNo], [Event_From], [Event_To], [Temp_Set], [Temp_Act], [Fan_Status])
    VALUES
        (SYSUTCDATETIME(), 2, 'CH-2026-0044', 'Heating', 'Soaking', 950.000, 949.000, 1);

    UPDATE [dbo].[tblEvent]
    SET [Event_To] = 'Soaking'
    WHERE [ID] = @PreviousEventID
      AND [SQF_No] = 2;

    IF @@ROWCOUNT <> 1
        THROW 50001, 'Expected one previous event row.', 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

XACT_ABORT ON helps ensure many runtime errors terminate and roll back the transaction. TRY/CATCH lets the script explicitly handle and rethrow errors.

18. Validate the Affected-Row Count

For a key-based operator action, zero rows and two rows can both be failures. Store @@ROWCOUNT immediately because the next statement can change it.

DECLARE @EventID int = 1250;
DECLARE @Rows int;

UPDATE [dbo].[tblEvent]
SET [Fan_Status] = 0
WHERE [ID] = @EventID;

SET @Rows = @@ROWCOUNT;

IF @Rows <> 1
    THROW 50002, 'Expected to update exactly one tblEvent row.', 1;

For optimistic concurrency, include the original value or a rowversion token in the predicate. A zero-row result then means another writer changed the row or the row no longer exists.

19. Upsert and Duplicate Protection

An upsert inserts a missing row or updates an existing one. Define the business key first and enforce it with a unique constraint. For simple cases, explicit UPDATE-then-INSERT logic is easier to reason about than a complex MERGE.

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE [dbo].[Equipment] WITH (UPDLOCK, SERIALIZABLE)
SET [EquipmentName] = @EquipmentName,
    [IsActive] = @IsActive
WHERE [EquipmentID] = @EquipmentID;

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO [dbo].[Equipment]
        ([EquipmentID], [EquipmentName], [IsActive])
    VALUES
        (@EquipmentID, @EquipmentName, @IsActive);
END;

COMMIT TRANSACTION;

20. NULL, DEFAULT and Conversion Rules

• NULL means unknown, missing or not applicable; test with IS NULL.
• Omitting a column can activate its default; explicitly inserting NULL does not request the default.
• COALESCE selects the first non-null expression, but do not hide missing sensor quality unintentionally.
• Use TRY_CONVERT when profiling untrusted staging text; failed conversions return NULL.
• Use ISO 8601 timestamps and an explicit UTC policy for system integration.

SELECT
    [ID],
    [Temp_Act],
    CASE
        WHEN [Temp_Act] IS NULL THEN 'MISSING'
        WHEN [Temp_Act] > 1200 THEN 'HIGH'
        ELSE 'NORMAL'
    END AS [TemperatureQuality]
FROM [dbo].[tblEvent];

SELECT [RawTemp]
FROM [dbo].[tblEvent_Stage]
WHERE [RawTemp] IS NOT NULL
  AND TRY_CONVERT(decimal(9,3), [RawTemp]) IS NULL;

21. Use Parameterized SCADA Commands

Do not concatenate tag values, batch numbers or operator text into SQL syntax. Use placeholders supported by the client library and bind values with matching SQL types and sizes.

-- SQL text sent by a named-parameter client
INSERT INTO dbo.tblEvent
    (DT, SQF_No, ChargeNo, Event_From, Event_To, Temp_Set, Temp_Act, Fan_Status)
OUTPUT inserted.ID
VALUES
    (@DT, @SQF_No, @ChargeNo, @Event_From, @Event_To,
     @Temp_Set, @Temp_Act, @Fan_Status);

Parameters reduce SQL-injection risk and conversion errors, but validation and least privilege still matter. Do not give a Runtime account ownership or unrestricted write access.

22. Put the Write Contract in a Stored Procedure

A stored procedure centralizes validation, transaction behavior and permissions. Runtime can receive EXECUTE permission without direct update/delete rights on every table.

CREATE OR ALTER PROCEDURE [dbo].[usp_RecordEvent]
    @DT          datetime2(3),
    @SQF_No      int,
    @ChargeNo    varchar(50),
    @Event_From  varchar(100),
    @Event_To    varchar(100),
    @Temp_Set    decimal(9,3) = NULL,
    @Temp_Act    decimal(9,3) = NULL,
    @Fan_Status  bit = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Equipment WHERE EquipmentID = @SQF_No AND IsActive = 1)
        THROW 50010, 'Equipment is missing or inactive.', 1;

    INSERT INTO dbo.tblEvent
        (DT, SQF_No, ChargeNo, Event_From, Event_To, Temp_Set, Temp_Act, Fan_Status)
    OUTPUT inserted.ID, inserted.DT
    VALUES
        (@DT, @SQF_No, @ChargeNo, @Event_From, @Event_To, @Temp_Set, @Temp_Act, @Fan_Status);
END;
GO

This procedure definition is DDL; calling it with EXEC dbo.usp_RecordEvent ... performs DML. Applications still bind its parameter values rather than concatenating an EXEC string.

23. Batch Large DML Operations Deterministically

Large updates/deletes can block acquisition clients and grow the transaction log. Process a measured number of keys per transaction and make the selection order explicit.

WHILE 1 = 1
BEGIN
    ;WITH Batch AS
    (
        SELECT TOP (1000) [ID]
        FROM [dbo].[tblEvent_Stage]
        WHERE [ValidationStatus] = 'PROCESSED'
          AND [DT] < @CutoffUtc
        ORDER BY [ID]
    )
    DELETE s
    FROM [dbo].[tblEvent_Stage] AS s
    JOIN Batch AS b ON b.[ID] = s.[ID];

    IF @@ROWCOUNT = 0 BREAK;
END;

Batch size is workload-specific. Monitor transaction-log usage, lock waits, Runtime latency and execution time. An index supporting the batch predicate/order is usually essential.

24. Common DML Errors and Fixes

SELECT [ID], [DT], [SQF_No], [Event_From], [Event_To], [Temp_Set], [Temp_Act]
FROM [dbo].[tblEvent]
WHERE [ChargeNo] = 'CH-LAB-0001'
ORDER BY [DT], [ID];

SELECT
    [ChargeNo], COUNT_BIG(*) AS [Events],
    MIN([DT]) AS [StartedUtc], MAX([DT]) AS [LastEventUtc],
    MIN([Temp_Act]) AS [MinTemp], MAX([Temp_Act]) AS [MaxTemp],
    AVG(CONVERT(decimal(18,3), [Temp_Act])) AS [AvgTemp]
FROM [dbo].[tblEvent]
WHERE [ChargeNo] = 'CH-LAB-0001'
GROUP BY [ChargeNo];

BEGIN TRANSACTION;

DELETE FROM [dbo].[tblEvent]
OUTPUT deleted.[ID], deleted.[DT], deleted.[ChargeNo]
WHERE [ChargeNo] = 'CH-LAB-0001';

-- Confirm the rows are absent inside this transaction.
SELECT COUNT_BIG(*) AS [RowsRemaining]
FROM [dbo].[tblEvent]
WHERE [ChargeNo] = 'CH-LAB-0001';

ROLLBACK TRANSACTION;

-- Confirm rollback restored the rows.
SELECT COUNT_BIG(*) AS [RowsAfterRollback]
FROM [dbo].[tblEvent]
WHERE [ChargeNo] = 'CH-LAB-0001';