2

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:

Enter image description here

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.

3

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
    );
3

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.

  • This is exactly what I been doing in my stored procedures. +1. – John Odom Oct 19 '15 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.