How to
not do an "upsert" from one table to another. Yes, this example came from a real stored procedure. Yes, this stored procedure eventually caused problems. Some of the table and field names have been changed for this example.
ALTER PROCEDURE [dbo].[UpdateDestinationTable]AS
BEGIN
DECLARE @counter INT
DECLARE @recordCount INT
SET @counter = 1
-- How many records are in the source table?
SELECT @recordCount = COUNT(*)
FROM SOURCE_TABLE
-- Let's loop that many times!
WHILE @counter <= @recordCount
BEGIN
DECLARE @pur_WBSnum VARCHAR(15)
DECLARE @pur_matnum VARCHAR(40)
DECLARE @pur_receiptdt DATETIME
DECLARE @pur_receiptid VARCHAR(15)
DECLARE @pur_vendorid VARCHAR(10)
DECLARE @pur_qtyrecvd DECIMAL(10, 3)
DECLARE @pur_sloc VARCHAR(4)
DECLARE @pur_plant VARCHAR(4)
-- Populate our variables with values from a single record
SELECT @pur_WBSnum = pur_WBSnum,
@pur_matnum = pur_matnum,
@pur_receiptdt = pur_receiptdt,
@pur_receiptid = pur_receiptid,
@pur_vendorid = pur_vendorid,
@pur_qtyrecvd = pur_qtyrecvd,
@pur_sloc = pur_sloc,
@pur_plant = pur_plant
FROM SOURCETABLE
-- pur_id is an identity column in the source table.
-- There will be a record with a pur_id value that
-- matches the iteration of the loop we are on... right?
WHERE pur_id = @counter
-- Update the corresponding record in the destination table
UPDATE DESTINATION_TABLE
SET VENDOR_ID = @pur_vendorid,
QTY_RECVD = @pur_qtyrecvd,
SLOC = @pur_sloc,
PLANT = @pur_plant
WHERE PROJECT_ID = @pur_WBSnum
AND DAX_ITEMID = @pur_matnum
AND RECEIPT_DT = @pur_receiptdt
AND RECEIPT_ID = @pur_receiptid
-- Nothing was updated?
IF @@ROWCOUNT = 0
-- Then we must need to do an insert instead!
INSERT INTO DESTINATION_TABLE
(
PROJECT_ID,
DAX_ITEMID,
RECEIPT_DT,
RECEIPT_ID,
PO_ID,
VENDOR_ID,
QTY_RECVD,
INVENT_TRANS_ID,
DATAAREA_ID,
SLOC,
PLANT
)
VALUES (
@pur_WBSnum,
@pur_matnum,
@pur_receiptdt,
@pur_receiptid,
' ',
@pur_vendorid,
@pur_qtyrecvd,
' ',
' ',
@pur_sloc,
@pur_plant
)
-- Re-initialize our variables and increment the counter
SET @pur_WBSnum = NULL
SET @pur_matnum = NULL
SET @pur_receiptdt = ' '
SET @pur_receiptid = NULL
SET @pur_vendorid = NULL
SET @pur_qtyrecvd = 0
SET @pur_sloc = NULL
SET @pur_plant = NULL
SET @counter = @counter + 1
END
-- We can count on TRUCATE to restart the
-- identity column seed at 1, right?
TRUNCATE TABLE SOURCE_TABLE
END
