By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[menu_Temp]
(
[Date] [datetime] NOT NULL,
[Ref] [int] NOT NULL,
[Art] [char](60) NOT NULL,
[Dish] [char](60) NOT NULL,
[DateReg] [datetime] NOT NULL,
[Zone] [char](60) NOT NULL,
);
CREATE TABLE [dbo].[menu]
(
[Date] [datetime] NOT NULL,
[Ref] [int] NOT NULL,
[Art] [char](60) NOT NULL,
[Dish] [char](60) NOT NULL,
[DateReg] [datetime] NOT NULL,
[Zone] [char](60) NOT NULL,
);
INSERT INTO menu VALUES ('2017-12-25', 0, 'Gieger', 'Vindaloo', '2017-12-25', 'Upper');
INSERT INTO menu VALUES ('2017-12-25', 1, 'Dali', 'Kharai', '2017-12-25', 'Upper');
INSERT INTO menu_Temp VALUES ('2018-01-01', 0, 'Gieger', 'Chips', '2017-01-01', 'Lower');
INSERT INTO menu_Temp VALUES ('2018-01-01', 1, 'Dali', 'Kharai', '2018-01-01', 'Lower');
INSERT INTO menu_Temp VALUES ('2018-01-01', 2, 'Matisse', 'Madras', '2018-01-01', 'Upper');
5 rows affected
MERGE
dbo.menu
USING
dbo.menu_Temp
ON (menu_Temp.[Ref] = menu.[Ref]) -- Assumes [Ref] is the identifying column?
WHEN
MATCHED AND (menu_Temp.[DateReg] > menu.[DateReg])
THEN
UPDATE SET [Art] = menu_Temp.[Art],
[Dish] = menu_Temp.[Dish],
[Zone] = menu_Temp.[Zone],
[Date] = menu_Temp.[Date],
[DateReg] = menu_Temp.[DateReg]
WHEN
NOT MATCHED
THEN
INSERT (
[Date],
[Ref],
[Art],
[Dish],
[DateReg],
[Zone]
)
VALUES (
menu_Temp.[Date],
menu_Temp.[Ref],
menu_Temp.[Art],
menu_Temp.[Dish],
menu_Temp.[DateReg],
menu_Temp.[Zone]
)
;
2 rows affected
SELECT * FROM menu;
| Date | Ref | Art | Dish | DateReg | Zone |
|---|---|---|---|---|---|
| 25/12/2017 00:00:00 | 0 | Gieger | Vindaloo | 25/12/2017 00:00:00 | Upper |
| 01/01/2018 00:00:00 | 1 | Dali | Kharai | 01/01/2018 00:00:00 | Lower |
| 01/01/2018 00:00:00 | 2 | Matisse | Madras | 01/01/2018 00:00:00 | Upper |