Copy update append records to a table based on another one

1

I have a following table that I need to update based on records on another one.
My tbl1 columns are Product | Code | Price
MY tbl2 columns are Product | Code | Price | startdate | enddate | Update Type

tbl1 needs to be updated based on records from tbl2
Basically
if columns Product and Code are equal for both tables and price is different and current date between startdate and enddate then tbl1 price - tbl2 price and the column tbl2.UpdateType needs to be updated to C.

if columns tbl2.Product and tbl2.Code not found in tbl1 and current date between startdate and enddate then append new records to tbl1. and update the column tbl2.UpdateType to A

if tbl1.Product , tbl1.Code, tbl1.Price not found in tbl2 then delete the whole record from tbl1. and update tbl2.UpdateType to D

I would appreciate some help with the above dilemma.

Thanks

2

DECLARE @Out TABLE(Action nvarchar(10), Product varchar(10), Code varchar(10));
MERGE tbl1
USING
( SELECT Product, Code, Price FROM tbl2 WHERE GETDATE() BETWEEN startdate AND enddate) tbl2
ON tbl1.Product=tbl2.Product
AND tbl1.Code=tbl2.Code
WHEN MATCHED THEN UPDATE SET Price = tbl2.Price
WHEN NOT MATCHED THEN INSERT (Product, Code, Price)
VALUES (tbl2.Product, tbl2.Code, tbl2.Price)
OUTPUT Action$, Inserted.Product, Inserted.Code, Inserted.Price
INTO @Out;
UPDATE t
SET [Update Type]=CASE Action WHEN 'INSERT' THEN 'A' ELSE 'C' END
FROM @Out o
JOIN tbl2 t
ON o.Product = t.Product
AND o.Code=t.Code
AND GetDate() BETWEEN startdate AND enaddate;
GO

3

Just an observation: we avoid using BETWEEN for date / time range checks due to the "edge" conditions for the end-point. Usually preferable to do

WHERE StartDate >= @MyStartDateTime
AND EndDate  < @MyEndDateTimeRoundedToFollowingDay
4

We are on version 2005, I think the merge Statement came with 2008. Is there a way around it? Thanks

5

The suggestion that I made in your other thread ...

6

Although ... you should upgrade. Support for SQL 2005 ended in April 2016 - and that's assuming that you were paying for Extended Support. If you weren't then support ended long before then :frowning: