MERGE command in SQL Server 2005

Question

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.

Answer 1

I guess my question is: Why don't you first update records that exist, and then insert all the records that don't? What are you gaining by forcing yourself to trap PK violation errors?

UPDATE B
SET Tier1 = A.Tier1,
    Tier2 = A.Tier2,
    QtyOnOrder = A.QtyOnOrder,
    QtyOnHand = A.QtyOnHand,
    LocalQtyOnHand = A.LocalQtyOnHand,
    ItemCancelled = A.ItemCancelled,
    DueDate = A.DueDate,
    StartDate = A.StartDate,
    ExpiryDate = A.ExpiryDate,
    BestDeliveryOptionId = A.BestDeliveryOptionId
FROM @ItemStockFake A
INNER JOIN ItemStockFake B ON A.ITEMNO = B.ITEMNO;

INSERT INTO ItemStockFake (Tier1, Tier2, QtyOnOrder, QtyOnHand, LocalQtyOnHand, ItemCancelled, DueDate, StartDate, ExpiryDate, BestDeliveryOptionId)
SELECT Tier1, Tier2, QtyOnOrder, QtyOnHand, LocalQtyOnHand, ItemCancelled, DueDate, StartDate, ExpiryDate, BestDeliveryOptionId
FROM @ItemStockFake A
WHERE NOT EXISTS (
        SELECT 1 
        FROM ItemStockFake B
        WHERE B.ITEMNO = A.ITEMNO
    );

Answer 2

You can do it like this:

BEGIN TRAN

UPDATE     A
SET          [Tier1]                  = B.[Tier1]               
           , [Tier2]                  = B.[Tier2] 
           , [QtyOnOrder]             = B.[QtyOnOrder] 
           , [QtyOnHand]              = B.[QtyOnHand] 
           , [LocalQtyOnHand]         = B.[LocalQtyOnHand] 
           , [ItemCancelled]          = B.[ItemCancelled] 
           , [DueDate]                = B.[DueDate] 
           , [StartDate]              = B.[StartDate] 
           , [ExpiryDate]             = B.[ExpiryDate] 
           , [BestDeliveryOptionId]   = B.[BestDeliveryOptionId]
FROM       [dbo].[ItemStockFake] AS A
INNER JOIN @ItemStockFake        AS B
    ON     A.ITEMNO = B.ITEMNO

INSERT INTO [dbo].[ItemStockFake] (Tier1, Tier2, QtyOnOrder, QtyOnHand, LocalQtyOnHand, ItemCancelled, DueDate, StartDate, ExpiryDate, BestDeliveryOptionId)
SELECT     A.Tier1, A.Tier2, A.QtyOnOrder, A.QtyOnHand, A.LocalQtyOnHand, A.ItemCancelled, A.DueDate, A.StartDate, A.ExpiryDate, A.BestDeliveryOptionId
FROM       @ItemStockFake        AS A
LEFT JOIN  [dbo].[ItemStockFake] AS B
    ON     A.ITEMNO = B.ITEMNO
WHERE      B.ITEMNO IS NULL

COMMIT

The first query takes all rows that exist in ItemStockFake and updates them. The second query inserts all non-existing rows in ItemStockFake.