March 30, 2013 at 1:34 am
Hi ALL,
Need one help/suggestion in tsql using MERGE in sql server 2008.I'm using the below code to do upsert in one of our table.
MERGE [dbo].[Dly_Target_Comp] AS DST
USING [dbo].[Dly_Target_Comp_Temp] AS SRC
ON (
SRC.[DateKey] = DST.[DateKey]
AND
SRC.[Wghtd_WI_Key] = DST.[Wghtd_WI_Key]
)
WHEN NOT MATCHED THEN
INSERT ([DateKey], [Wghtd_WI_Key], [Item_Cd], [Metric_Desc], [Volume])
VALUES (SRC.[DateKey], SRC.[Wghtd_WI_Key], SRC.[Item_Cd], SRC.[Metric_Desc], SRC.[Volume])
WHEN MATCHED
AND (
ISNULL(DST.[Item_Cd],'') <> ISNULL(SRC.[Item_Cd],'')
OR ISNULL(DST.[Metric_Desc],'') <> ISNULL(SRC.[Metric_Desc],'')
OR ISNULL(DST.[Volume],'') <> ISNULL(SRC.[Volume],'')
)
THEN UPDATE
SET
DST.[Item_Cd] = SRC.[Item_Cd]
,DST.[Metric_Desc] = SRC.[Metric_Desc]
,DST.[Volume] = SRC.[Volume]
;
Now the requirement has changed, instead of doing update when there is a MATCH i need to do insert with the help of a new column called 'Projection_Date' . So there will same records but with different projection date.
But while trying to insert when matched, it says insert not allowed in 'when matched' clause.
Please advice.
Thanks in advance
Sam
April 1, 2013 at 2:02 am
Hi all,
Any suggestion please for the above issue..
April 1, 2013 at 9:41 am
Hi,
I would do two things:
Make sure there is a Projection_Date column in destination table. Then, on the last line of the merge statement I would add an additional SET statement eg. DST.projection_date =<script out the business rules for setting the projection date value, here, using CASE statements>
In my example I just used function getdate()+10 to simulate business rule.
DDL for sample data
create table SRC
(
keyid int,
item_cd int,
metric_desc varchar(20),
vol decimal,
weighted_key int
);
insert into SRC
VALUES
(1001, 233, 'obamastandard', 60, 655),
(1002, 200, 'mandelastandard', 70, 412),
(1003, 251, 'havelstandard', 80, 566),
(1004, 215, 'walesastandard', 90, 718);
create table DST
(
keyid int,
item_cd int,
metric_desc varchar(20),
vol decimal,
weighted_key int,
projection_date datetime)
;
insert into DST
VALUES
(1001, 233, 'obamastandard', 60, 655, getdate()+10);
Modified Merge statement
MERGE DST AS DST
USING SRC as SRC
ON (
SRC.[KeyID] = DST.[KeyID]
AND
SRC.weighted_key = DST.weighted_key
)
WHEN NOT MATCHED THEN
INSERT (Keyid, weighted_key, [Item_Cd], [Metric_Desc], [Vol])
VALUES (SRC.[Keyid], SRC.weighted_key, SRC.[Item_Cd], SRC.[Metric_Desc], SRC.[Vol])
WHEN MATCHED
AND (
ISNULL(DST.[Item_Cd],'') <> ISNULL(SRC.[Item_Cd],'')
OR ISNULL(DST.[Metric_Desc],'') <> ISNULL(SRC.[Metric_Desc],'')
OR ISNULL(DST.[Vol],'') <> ISNULL(SRC.[Vol],'')
)
THEN UPDATE
SET
DST.[Item_Cd] = SRC.[Item_Cd]
,DST.[Metric_Desc] = SRC.[Metric_Desc]
,DST.[Vol] = SRC.[Vol]
,DST.projection_date = getdate()+10
;
April 1, 2013 at 12:50 pm
You can use a feature called 'Composable DML' that allows the output of a DML statement (such as MERGE) to feed another one - basically using an OUTPUT clause in a query as a derived table. In the OUTPUT clause, you can add the $action function that will be either 'INSERT', 'UPDATE', or 'DELETE', depending on what the action on that row was. If you OUTPUT the results of the merge, including the $action function, then filter the results where the $action = 'UPDATE', then you get a list of everything touched in your WHEN MATCHED...UPDATE block. You can use that to feed the INSERT statement to add new rows (not sure where you get the extra data column from, just add that in to the query below, either from the MERGE or through a JOIN):
INSERT [dbo].[Dly_Target_Comp](<your target columns go here)>)
SELECT a.Item_Code, a.Metric_Desc, a.Volume
FROM (
MERGE [dbo].[Dly_Target_Comp] AS DST
USING [dbo].[Dly_Target_Comp_Temp] AS SRC
ON (SRC.[DateKey] = DST.[DateKey] AND SRC.[Wghtd_WI_Key] = DST.[Wghtd_WI_Key])
WHEN NOT MATCHED THEN
INSERT ([DateKey], [Wghtd_WI_Key], [Item_Cd], [Metric_Desc], [Volume])
VALUES (SRC.[DateKey], SRC.[Wghtd_WI_Key], SRC.[Item_Cd], SRC.[Metric_Desc], SRC.[Volume])
WHEN MATCHED
AND (ISNULL(DST.[Item_Cd],'') <> ISNULL(SRC.[Item_Cd],'') OR ISNULL(DST.[Metric_Desc],'') <> ISNULL(SRC.[Metric_Desc],'')
OR ISNULL(DST.[Volume],'') <> ISNULL(SRC.[Volume],'') THEN
UPDATE SET DST.[Item_Cd] = SRC.[Item_Cd], DST.[Metric_Desc] = SRC.[Metric_Desc],DST.[Volume] = SRC.[Volume]
OUTPUT $action, inserted.Item_Cd, inserted.Metric_Desc, inserted.Volume
) AS a(ModType, Item_Code, Metric_Desc, Volume)
WHERE a.ModType = 'UPDATE'
Eddie Wuerch
MCM: SQL
April 2, 2013 at 11:25 pm
Thanks Eddie for your suggestion, will definitely try this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply