Sql-server – MERGE command in SQL Server 2005

I have been trying to develop a MERGE in SQL Server 2005. It does not have all features yet, because I have been struggling in "how to put the merge logic WORKING AS A SET, rather than rows".

What I have so far has basically nothing do to do with MERGE – just "if the record is already there, delete" and then do a full insert (approximately 8,000 rows).

But it does the job – working on sets.

This is my table – ItemStockFake:

I declare the source:

SET NOCOUNT ON


declare @ItemStockFake table(
    [ItemNo] [varchar](10) NOT NULL primary key clustered,
    [Tier1] [varchar](10) NULL,
    [Tier2] [varchar](10) NULL,
    [QtyOnOrder] [int] NOT NULL,
    [QtyOnHand] [int] NOT NULL,
    [LocalQtyOnHand] [int] NOT NULL,
    [ItemCancelled] [bit] NULL,
    [DueDate] [smalldatetime] NULL,
    [StartDate] [smalldatetime] NOT NULL,
    [ExpiryDate] [smalldatetime] NOT NULL,
    [BestDeliveryOptionId] [int] NOT NULL
)

Write to the source (only a few records – there are thousands in real life):

INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623685','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623693','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623701','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623719','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623727','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')

And then do the "magic" – basically delete the records from the database, before inserting them:

BEGIN TRY

            --=====================================================================================
            -- A T T E N T I O N - DELETING THE RECORDS CURRENT IN THE DATABASE
            -- to make room for the new inserts
            --=====================================================================================

            BEGIN TRANSACTION T1


            DELETE B
            FROM @ItemStockFake A
            INNER JOIN ItemStockFake B ON A.ITEMNO = B.ITEMNO

            PRINT CAST( @@ROWCOUNT  AS VARCHAR) + ' number of records deleted.'

            INSERT INTO [dbo].[ItemStockFake]
            SELECT * FROM @ItemStockFake

            PRINT CAST( @@ROWCOUNT  AS VARCHAR) + ' number of records inserted.'

            COMMIT TRANSACTION T1

END TRY

BEGIN CATCH

        WHILE @@TRANCOUNT > 0
              ROLLBACK TRANSACTION



        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13)

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH

No big deal.

What I would like to implement is something like this:

...
BEGIN TRY
   INSERT table1
END TRY
BEGIN CATCH
   IF ERROR_NUMBER = 2627
       UPDATE table1
   ELSE
       -- Process the real error
END CATCH
...

which comes from this question here.

I would like to work in sets though, and get this whole operation done as quick as possible.