sql-serversql-server-2012sql-merge

MERGE will not work on production server as it is still SQL Server 2012

I have the following script which works on our SQL Server 2016, but throws the following error on production, which unfortunately is still running SQL Server 2012:

Msg 325, Level 15, State 1, Procedure spUpdateAlertHourlyCounts, Line 62
Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature.

Msg 156, Level 15, State 1, Procedure spUpdateAlertHourlyCounts, Line 63
Incorrect syntax near the keyword 'AS'.

The script is:

USE [AutoTicketDB_Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Mych
-- Create date: 15/01/2024
-- Description: Script that will grab the hourly alert incidents count for a particular date and save this to the Alert_HourlyCounts table
-- =============================================
CREATE PROCEDURE [dbo].[spUpdateAlertHourlyCounts] 
    -- Add the parameters for the stored procedure here
    @DateForData DATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ThisDate DATE
    SET @ThisDate = CAST(Convert(varchar(10), @DateForData,120) As DATE);

    DECLARE @StartDate varchar(10); 
    SET @StartDate = CONVERT(varchar(10), @ThisDate, 126);
    PRINT 'StartDate = ' + @StartDate;

    DECLARE @EndDate nvarchar(10);
    SET @EndDate = CONVERT(varchar(10), DATEADD(DAY, 1, @ThisDate), 126);
    PRINT 'EndDate = ' + @EndDate;

    -- This statement will get the hourly data for a given date and store this data in a temporary table
    SELECT @StartDate As AlertDate, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 00:00:00', 120) AND Convert(datetime, @StartDate + ' 01:00:00', 120)), 0) As H00, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 01:00:00', 120) AND Convert(datetime, @StartDate + ' 02:00:00', 120)), 0) As H01, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 02:00:00', 120) AND Convert(datetime, @StartDate + ' 03:00:00', 120)), 0) As H02, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 03:00:00', 120) AND Convert(datetime, @StartDate + ' 04:00:00', 120)), 0) As H03, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 04:00:00', 120) AND Convert(datetime, @StartDate + ' 05:00:00', 120)), 0) As H04, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 05:00:00', 120) AND Convert(datetime, @StartDate + ' 06:00:00', 120)), 0) As H05, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 06:00:00', 120) AND Convert(datetime, @StartDate + ' 07:00:00', 120)), 0) As H06, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 07:00:00', 120) AND Convert(datetime, @StartDate + ' 08:00:00', 120)), 0) As H07, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 08:00:00', 120) AND Convert(datetime, @StartDate + ' 09:00:00', 120)), 0) As H08, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 09:00:00', 120) AND Convert(datetime, @StartDate + ' 10:00:00', 120)), 0) As H09, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 10:00:00', 120) AND Convert(datetime, @StartDate + ' 11:00:00', 120)), 0) As H10, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 11:00:00', 120) AND Convert(datetime, @StartDate + ' 12:00:00', 120)), 0) As H11, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 12:00:00', 120) AND Convert(datetime, @StartDate + ' 13:00:00', 120)), 0) As H12, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 13:00:00', 120) AND Convert(datetime, @StartDate + ' 14:00:00', 120)), 0) As H13, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 14:00:00', 120) AND Convert(datetime, @StartDate + ' 15:00:00', 120)), 0) As H14, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 15:00:00', 120) AND Convert(datetime, @StartDate + ' 16:00:00', 120)), 0) As H15, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 16:00:00', 120) AND Convert(datetime, @StartDate + ' 17:00:00', 120)), 0) As H16, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 17:00:00', 120) AND Convert(datetime, @StartDate + ' 18:00:00', 120)), 0) As H17, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 18:00:00', 120) AND Convert(datetime, @StartDate + ' 19:00:00', 120)), 0) As H18, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 19:00:00', 120) AND Convert(datetime, @StartDate + ' 20:00:00', 120)), 0) As H19, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 20:00:00', 120) AND Convert(datetime, @StartDate + ' 21:00:00', 120)), 0) As H20, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 21:00:00', 120) AND Convert(datetime, @StartDate + ' 22:00:00', 120)), 0) As H21, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 22:00:00', 120) AND Convert(datetime, @StartDate + ' 23:00:00', 120)), 0) As H22, 
    ISNULL((SELECT Count(ID) FROM [dbo].[ALERT_Requests] WHERE snIncidentCreated BETWEEN Convert(datetime, @StartDate + ' 23:00:00', 120) AND Convert(datetime, @EndDate + ' 00:00:00', 120)), 0) As H23
    INTO #tempAlertHourlyCount
    FROM [dbo].[ALERT_Requests]
    GROUP BY [Alert_source]


    --This statement will check if there is already a row for the date the data was collected. 
    --If there is already a row then the row is updated with the new data
    --If the date does not exist the new data is inserted as a new row.
    MERGE INTO dbo.Alert_HourlyCounts AS target
    USING (SELECT AlertDate, H00, H01, H02, H03, H04, H05, H06, H07, H08, H09, H10, H11, H12, H13, H14, H15, H16, H17, H18, H19, H20, H21, H22, H23 from #tempAlertHourlyCount) AS source
    ON (target.AlertDate = source.AlertDate)
    WHEN MATCHED THEN
        UPDATE SET H00 = source.H00, H01 = source.H01, H02 = source.H02, H03 = source.H03, H04 = source.H04, H05 = source.H05, H06 = source.H06, H07 = source.H07, 
                   H08 = source.H08, H09 = source.H09, H10 = source.H10, H11 = source.H11, H12 = source.H12, H13 = source.H13, H14 = source.H14, H15 = source.H15, 
                   H16 = source.H16, H17 = source.H17, H18 = source.H18, H19 = source.H19, H20 = source.H20, H21 = source.H21, H22 = source.H22, H23 = source.H23
    WHEN NOT MATCHED THEN
        INSERT (AlertDate, H00, H01, H02, H03, H04, H05, H06, H07, H08, H09, H10, H11, H12, H13, H14, H15, H16, H17, H18, H19, H20, H21, H22, H23) 
        VALUES (source. AlertDate, source.H00, source.H01, source.H02, source.H03, source.H04, source.H05, source.H06, source.H07, 
                source.H08, source.H09, source.H10, source.H11, source.H12, source.H13, source.H14, source.H15, 
                source.H16, source.H17, source.H18, source.H19, source.H20, source.H21, source.H22, source.H23);

    --This statement will dispose of the temporary table
    DROP TABLE #tempAlertHourlyCount

END
GO

Is there a way to achieve this in SQL Server 2012? We will be upgrading this server to SQL Server 2019 but not in time.

Solution

  • I have solved my problem by trying an update first if now rows affected then I do an insert.

    UPDATE dbo.Alert_HourlyCounts SET H00 = t.H00, H01 = t.H01, H02 = t.H02, H03 = t.H03, H04 = t.H04, H05 = t.H05, H06 = t.H06, H07 = t.H07, 
                                H08 = t.H08, H09 = t.H09, H10 = t.H10, H11 = t.H11, H12 = t.H12, H13 = t.H13, H14 = t.H14, H15 = t.H15, 
                                H16 = t.H16, H17 = t.H17, H18 = t.H18, H19 = t.H19, H20 = t.H20, H21 = t.H21, H22 = t.H22, H23 = t.H23 
                                FROM dbo.Alert_HourlyCounts As o
                                JOIN #tempAlertHourlyCount t ON o.AlertDate = t.AlertDate

-- the AlertDate does not exist the the @@ROWCOUNT will be 0 so we do an Insert
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO dbo.Alert_HourlyCounts (AlertDate, H00, H01, H02, H03, H04, H05, H06, H07, H08, H09, H10, H11, H12, H13, H14, H15, H16, H17, H18, H19, H20, H21, H22, H23)
    SELECT AlertDate, H00, H01, H02, H03, H04, H05, H06, H07, H08, H09, H10, H11, H12, H13, H14, H15, H16, H17, H18, H19, H20, H21, H22, H23 FROM #tempAlertHourlyCount
END;

--This statement will dispose of the temporary table
DROP TABLE #tempAlertHourlyCount