merge to output matched and not matched
-
Hi, I am using a merge statement in my Stored Proc, when I am trying to get inserted values, its giving me all the matched and unmatched ones. In the example below:
MERGE [dbo].[Service_Program_Detail] AS T
USING (select *, @PK_Target 'PK_Target' from [dbo].[Service_Program_Detail]
where FK_Program_Code_ServiceProgDetails = @PK_Source
and FK_Fiscal_Period_Code_ServiceProgDetails=@Fiscal_Period_Code) AS S
ON ((T.FK_Service_Code_ServiceProgDetails = S.FK_Service_Code_ServiceProgDetails)
and (T.FK_Fiscal_Period_Code_ServiceProgDetails=S.FK_Fiscal_Period_Code_ServiceProgDetails)
and (T.FK_Program_Code_ServiceProgDetails=S.PK_Target))
WHEN NOT MATCHED
THEN INSERT(FK_Service_Code_ServiceProgDetails, FK_Program_Code_ServiceProgDetails
, FK_Fiscal_Period_Code_ServiceProgDetails, CreatedBy, ModifiedBy
, CreatedOn, ModifiedOn, FK_Service_Program_Detail_Unit_Types)
VALUES(S.FK_Service_Code_ServiceProgDetails, S.PK_Target
, S.FK_Fiscal_Period_Code_ServiceProgDetails, @ExecutedBy, @ExecutedBy,
getdate(), getdate(), FK_Service_Program_Detail_Unit_Types)
WHEN MATCHED
THEN UPDATE SET T.FK_Service_Program_Detail_Unit_Types = S.FK_Service_Program_Detail_Unit_Types
OUTPUT inserted.PK_Service_Program_Detail,
inserted.FK_Service_Code_ServiceProgDetails,
@PK_Source,
inserted.FK_Program_Code_ServiceProgDetails,
inserted.FK_Fiscal_Period_Code_ServiceProgDetails
INTO @Service_Program_Detail
(
PK_Service_Program_Detail_T,
FK_Service_Code_ServiceProgDetails,
Program_Code_S,
Program_Code_T,
FK_Fiscal_Period_Code_ServiceProgDetails
);the output statement is inserting not matched values and matched values, but I want only not matched values to be inserted into @Service_Program_Detail. My question is, is there any possibility that I can output matched and not matched values separately or I want only not matched values (which I could able to do by putting only not matched condition, but I want to update the value as well but it shouldn't be inserted into @Service_Program_Detail and it should be able to go into a different value or table variable. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi, I am using a merge statement in my Stored Proc, when I am trying to get inserted values, its giving me all the matched and unmatched ones. In the example below:
MERGE [dbo].[Service_Program_Detail] AS T
USING (select *, @PK_Target 'PK_Target' from [dbo].[Service_Program_Detail]
where FK_Program_Code_ServiceProgDetails = @PK_Source
and FK_Fiscal_Period_Code_ServiceProgDetails=@Fiscal_Period_Code) AS S
ON ((T.FK_Service_Code_ServiceProgDetails = S.FK_Service_Code_ServiceProgDetails)
and (T.FK_Fiscal_Period_Code_ServiceProgDetails=S.FK_Fiscal_Period_Code_ServiceProgDetails)
and (T.FK_Program_Code_ServiceProgDetails=S.PK_Target))
WHEN NOT MATCHED
THEN INSERT(FK_Service_Code_ServiceProgDetails, FK_Program_Code_ServiceProgDetails
, FK_Fiscal_Period_Code_ServiceProgDetails, CreatedBy, ModifiedBy
, CreatedOn, ModifiedOn, FK_Service_Program_Detail_Unit_Types)
VALUES(S.FK_Service_Code_ServiceProgDetails, S.PK_Target
, S.FK_Fiscal_Period_Code_ServiceProgDetails, @ExecutedBy, @ExecutedBy,
getdate(), getdate(), FK_Service_Program_Detail_Unit_Types)
WHEN MATCHED
THEN UPDATE SET T.FK_Service_Program_Detail_Unit_Types = S.FK_Service_Program_Detail_Unit_Types
OUTPUT inserted.PK_Service_Program_Detail,
inserted.FK_Service_Code_ServiceProgDetails,
@PK_Source,
inserted.FK_Program_Code_ServiceProgDetails,
inserted.FK_Fiscal_Period_Code_ServiceProgDetails
INTO @Service_Program_Detail
(
PK_Service_Program_Detail_T,
FK_Service_Code_ServiceProgDetails,
Program_Code_S,
Program_Code_T,
FK_Fiscal_Period_Code_ServiceProgDetails
);the output statement is inserting not matched values and matched values, but I want only not matched values to be inserted into @Service_Program_Detail. My question is, is there any possibility that I can output matched and not matched values separately or I want only not matched values (which I could able to do by putting only not matched condition, but I want to update the value as well but it shouldn't be inserted into @Service_Program_Detail and it should be able to go into a different value or table variable. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
In the output clause you can specify the
$actioncolumn that tells you whether it was an insert, update or a delete. And since you only have updates when matched...Wrong is evil and must be defeated. - Jeff Ello