Click here to Skip to main content
14,771,489 members
Home » Discussions » Database
   

Database

 
SuggestionRe: Identity behavior in sql server 2008 and 2012 Pin
CHill608-Mar-18 9:04
mveCHill608-Mar-18 9:04 
AnswerRe: Identity behavior in sql server 2008 and 2012 Pin
Richard Deeming8-Mar-18 10:14
mveRichard Deeming8-Mar-18 10:14 
PraiseRe: Identity behavior in sql server 2008 and 2012 Pin
CHill608-Mar-18 10:37
mveCHill608-Mar-18 10:37 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
VK199-Mar-18 8:43
MemberVK199-Mar-18 8:43 
AnswerRe: Identity behavior in sql server 2008 and 2012 Pin
jschell10-Mar-18 11:30
Memberjschell10-Mar-18 11:30 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
VK1912-Mar-18 3:27
MemberVK1912-Mar-18 3:27 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
jschell14-Mar-18 16:16
Memberjschell14-Mar-18 16:16 
Questionmerge to output matched and not matched Pin
indian14328-Feb-18 12:09
Memberindian14328-Feb-18 12:09 
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."
AnswerRe: merge to output matched and not matched Pin
Jörgen Andersson7-Mar-18 12:07
professionalJörgen Andersson7-Mar-18 12:07 
Questionsql query to get code with max rank Pin
Member 927852427-Feb-18 7:55
MemberMember 927852427-Feb-18 7:55 
AnswerRe: sql query to get code with max rank Pin
Richard Deeming27-Feb-18 8:58
mveRichard Deeming27-Feb-18 8:58 
AnswerRe: sql query to get code with max rank Pin
Paket Wisata Lombok28-Feb-18 7:01
professionalPaket Wisata Lombok28-Feb-18 7:01 
QuestionUnable to find Postgresql data source in Entity Data Model Wizard Pin
Sudhir Dehade27-Feb-18 2:19
MemberSudhir Dehade27-Feb-18 2:19 
Rant[REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Richard Deeming27-Feb-18 3:12
mveRichard Deeming27-Feb-18 3:12 
RantRe: [REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Richard Deeming27-Feb-18 3:20
mveRichard Deeming27-Feb-18 3:20 
GeneralRe: [REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Richard Deeming27-Feb-18 3:26
mveRichard Deeming27-Feb-18 3:26 
GeneralRe: [REPOST] Unable to find Postgresql data source in Entity Data Model Wizard Pin
Sudhir Dehade27-Feb-18 3:30
MemberSudhir Dehade27-Feb-18 3:30 
QuestionTable Database Advice Pin
JackMisani22-Feb-18 1:36
MemberJackMisani22-Feb-18 1:36 
AnswerRe: Table Database Advice Pin
Richard Deeming22-Feb-18 1:53
mveRichard Deeming22-Feb-18 1:53 
GeneralRe: Table Database Advice Pin
JackMisani22-Feb-18 3:10
MemberJackMisani22-Feb-18 3:10 
GeneralRe: Table Database Advice Pin
Mycroft Holmes22-Feb-18 13:15
professionalMycroft Holmes22-Feb-18 13:15 
GeneralRe: Table Database Advice Pin
JackMisani25-Feb-18 23:43
MemberJackMisani25-Feb-18 23:43 
AnswerRe: Table Database Advice Pin
Eddy Vluggen22-Feb-18 7:51
professionalEddy Vluggen22-Feb-18 7:51 
AnswerRe: Table Database Advice Pin
jschell23-Feb-18 14:44
Memberjschell23-Feb-18 14:44 
GeneralRe: Table Database Advice Pin
JackMisani25-Feb-18 23:49
MemberJackMisani25-Feb-18 23:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.