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.