none
issue with merge in output when update

    Question

  • Problem - I put everything in a store procedure.Initially source.efforts=10. When I execute 1st time it is working fine.

    • Later when I updated source.efforts=15 and ran stored procedure update not working. I cannot see update value changed in table3 target table values also not updated correctly.
    • when i execute same stored procedure then am getting right value.I'm not able to figure it out.
    • When I comment bold lines then it is working but i want capture the conditions.

    MERGE table1 AS target
    USING (
        SELECT a.aid,a.efforts,ab.lcs
        FROM cip a
        INNER JOIN dc ab ON a id = ab.id
        WHERE a.id = @id
        ) AS source
        ON ( target.caseid = @caseidupd
                AND target.aid = source.aid
                )
    WHEN MATCHED AND source.lc IN (1,2)
        THEN
            UPDATE
            SET target curr = CASE
                    WHEN source.efforts = 0
                        THEN source.efforts source.efforts <> 0 then source.efforts
                    END
        SET target tar= CASE
                    WHEN source.efforts = 0
                        THEN source.efforts source.efforts <> 0 then target.curr
                    END
                ,target.modi = CASE
                    WHEN source.efforts = 0
                        THEN getdate() source.efforts <> 0 then getdate()
                    END
    WHEN NOT MATCHED BY target
        THEN
            INSERT (caseid,aid,modi)
            VALUES (@caseidupd,source.aid,getdate())
    output $action
    case
    when $action='INSERT' THEN 'APPINSERT'
    case when $action='UPDATE' THEN 'APPzeroed'
    case when $action='UPDATE' THEN 'source value' +curr +'changed to'+tar

    INTO table3




    • Edited by kdinuk Thursday, May 01, 2014 9:29 PM
    Thursday, May 01, 2014 9:09 PM

Answers

  • It's hard to say too much from what you have given us.  You have not given us table definitions or data and the code you show has syntax errors in it (for example, you reference source.ait in your ON condition, but there is no column named ait in your source derived table.

    One guess I have is that one or more of the columns you are concatenating to create the text column in the third case is NULL.  If that is the case, the whole expression is NULL.  You would need to use ISNULL() or COALESCE() functions (or if you are on SQL 2012 or later you could use the CONCAT() function).

    If that's not the case, I would recommend you give some code that we can run that demonstrates your problem.  For example, something like the following code.  The following code will show a procedure that uses a merge statement to work somewhat like what you have shown us, and is able to output all three cases.

    use tempdb
    go
    Create Table #cip(nid varchar(5), id varchar(5), efforts varchar(5), curr varchar(5), tar varchar(5));
    Create Table #cas(nid varchar(5), l_id varchar(5));
    Create Table #t1(caseid varchar(5), aid varchar(5), modi datetime, curr varchar(5), tar varchar(5));
    Create Table #table3(action char(6), text varchar(150), efforts varchar(5), currinserted varchar(5), currdeleted varchar(5), tarinserted varchar(5), tardeleted varchar(5));
    go
    
    Create Procedure FooProc(@caseid int, @nid int) As
    BEGIN
    MERGE #t1 AS target
    USING (SELECT a.id As aid,a.efforts,b.l_id, a.curr, a.tar FROM #cip a INNER JOIN #cas b ON a.nid=b.nid WHERE a.nid=@nid) AS source
    ON (target.caseid=@caseid AND target.aid=source.aid)
    WHEN MATCHED/* AND EXISTS
    (SELECT source.efforts, target.curr
    EXCEPT
    SELECT target.curr,target.tar) AND source.l_id IN (2,3) */
    THEN
    UPDATE SET target.curr=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    		   target.tar=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    			target.modi=CASE WHEN source.efforts=0 THEN GETDATE()
    							WHEN SOURCE.efforts<>0 THEN GETDATE() END
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT (caseid,aid,modi)
            VALUES (@caseid,IsNull(source.aid, '1'),getdate())
    output $action,
    case
    when $action='INSERT' THEN 'APPINSERT'+IsNull(source.aid,'')
    when $action='UPDATE' and source.efforts=0 THEN 'APPzeroed' + IsNull(source.aid,'')
    when $action='UPDATE' THEN 'source value' + IsNull(source.curr,'') +'changed to'+ IsNull(source.tar,'') end text,source.efforts, inserted.curr, deleted.curr, inserted.tar, deleted.tar
    INTO #table3;
    END;
    go
    
    Insert #cip(nid, id, efforts, curr, tar) Values 
    (1, 1, 2, 4, 8);
    Insert #cas(nid, l_id) Values
    (1, 16)
    ,(2, 32);
    -- This should output 1st case
    Exec FooProc 1,1;
    -- Check
    select 'First case', * from #table3
    -- This should output 2nd case
    Delete From #table3;
    Update #cip Set efforts = 0;
    Exec FooProc 1,1;
    -- Check
    select 'Second case', * from #table3
    -- This should output 3rd case
    Delete From #table3;
    Update #cip Set efforts = 1;
    Exec FooProc 1,1;
    -- Check
    select 'Third case', * from #table3
    go
    Drop Procedure FooProc;
    go
    Drop Table #cas;
    go
    Drop Table #cip;
    go
    Drop Table #t1;
    go
    Drop Table #table3;
    Tom

     
    Friday, May 02, 2014 4:32 AM
  • I have a question - how do i find out the caseid existing or not?

    So what is this jazz about the caseid existing or not? I have seen a lot of code in this thread, but I might have read the text around it poorly, because I don't understand what you are trying to achieve. Maybe you should start over from the beginning and telling us what your misson from start to end.

    But some comments on the code you posted:

    DECLARE db_cursor CURSOR FOR SELECT DISTINCT FROM #ip
    OPEN db_cursor
    FETCH NEXT FROM db_cursor
    INTO @nid
    BEGIN
    WHILE @@FETCH_STATUS=0
    BEGIN
         SET @ecaseid=-1;
         SET @ncaseid=-1;
         SET @casetoupd=-1
         SELECT TOP 1 @ecaseid=caseid FROM #ip ORDER BY modi

    First you run a cursor over #ip, and the you do a SELECT TOP 1 from it?

    And there is no FETCH and the end of the cursor loop?

    THEN
    UPDATE SET target.curr=CASE WHEN source.efforts=0 THEN SOURCE.efforts
                                       WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
                 target.tar=CASE WHEN source.efforts=0 THEN SOURCE.efforts
                                       WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
                   target.modi=CASE WHEN source.efforts=0 THEN GETDATE()
                                       WHEN SOURCE.efforts<>0 THEN GETDATE() END

    I have already pointed this out once. These CASE expressions appears to completely pointless. No matter the value of Source.efforts, you will set the target columns to the same value. (Or can source.efforts be NULL?)

    And then there are things like:

    IF (@ecaseid = -1) OR ((@ecaseid >-1) AND L_id  in (2,3))

    L_id is not a varible and there is no column, so this is just incorrect SQL.

    IF @ecaseid >-1)

    And the right parenthesis is just incorrect syntax.

    It can be difficult to understand what people are trying to achieve from code that actually compiles and runs but does not produce the correct result. Here we are supposed to understand what you are trying to do from code that is not close to compile.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 03, 2014 10:21 AM

All replies

  • I didn't look into your code, but in your CASE expression you're using the exactly same condition twice

    case when $action='UPDATE' THEN 'APPzeroed'
    case when $action='UPDATE' THEN 'source value' +curr +'changed to'+tar

    Therefore the second case will never execute.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, May 01, 2014 9:53 PM
  • What do you want to achieve? Can you post corrected last part of that command?

    I took one more look at your command and it seems to have many errors. I think it should throw a syntax error in the compile time, not run-time.

    Can you please post the actual command you're using without syntax errors?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, May 01, 2014 10:00 PM
  • OOPS my mistake.

    What should I use? I put like this...but still its not working. If i comment the line which is in BOLD then it is working

    output $action
    case
    when $action='INSERT' THEN 'APPINSERT'
    case when $action='UPDATE' and source=0 THEN 'APPzeroed'
    case when $action='UPDATE' THEN 'source value' +curr +'changed to'+tar end text,source.efforts,inserted.curr,deleted.curr,inserted.tar,deleted.tar

    I have these fields in table3= action, text,efforts, newcurr, oldcurr, newtar,oldtar

    Thursday, May 01, 2014 10:14 PM
  • There is something wrong in how your code is relayed to the forum:

            SET target curr = CASE
                    WHEN source.efforts = 0
                        THEN source.efforts source.efforts <> 0 then source.efforts
                    END

    This is not legal syntax. Something is missing.

    There is a funny button in the web UI with two arrows pointing from each other. This button permits you to insert code without the web UI mutilating the code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 01, 2014 10:22 PM
  • First of all thanks for your help...Here is code...Basically, i am checking old & new values and updating table3 that includes messages which in put in output clause @caseid & @nid, am going to send values dynamically. If I comment underline code then it is working but i am not able to capture updates happend so i can't insert values under "text" field in table3

    I have these fields in table3= action, text,efforts, newcurr, oldcurr, newtar,oldtar
    BEGIN
    MERGE t1 AS target
    USING (SELECT a.id,a.efforts,ab.l_id FROM cip a INNER JOIN cas b ON a.nid=ab.nid WHERE a.nid=@nid) AS source
    ON (target.caseid=@caseid AND target.aid=source.ait)
    WHEN MATCHED AND EXISTS
    (SELECT source.efforts, target.curr
    EXCEPT
    SELECT target.curr,target.tar) AND source.l_id (2,3)
    THEN
    UPDATE SET target.cur=CASE WHEN soure.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    		   target.tar=CASE WHEN soure.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    			target.mod=CASE WHEN soure.efforts=0 THEN GETDATE()
    							WHEN SOURCE.efforts<>0 THEN GETDATE() END
    END
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT INTO INSERT (caseid,aid,modi)
            VALUES (@caseid,source.aid,getdate())
    output $action
    case
    when $action='INSERT' THEN 'APPINSERT'+source.aid,
    case when $action='UPDATE' and source=0 THEN 'APPzeroed' + source.aid
    case when $action='UPDATE' THEN 'source value' +curr +'changed to'+tar end text,source.efforts,inserted.curr,deleted.curr,inserted.tar,deleted.tar
    INTO table3





    • Edited by kdinuk Thursday, May 01, 2014 10:47 PM
    Thursday, May 01, 2014 10:38 PM
  • Yes, looks like forum's interface is eating commas in your code. Please re-post it using the special code button and clearly state what exactly do you want to achieve.

    I also don't understand what do you check with the EXISTS code.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, May 01, 2014 10:40 PM
  • This part

    output $action
    case
    when $action='INSERT' THEN 'APPINSERT'+source.aid,
    case when $action='UPDATE' and source=0 THEN 'APPzeroed' + source.aid
    case when $action='UPDATE' THEN 'source value' +curr +'changed to'+tar end text,source.efforts,inserted.curr,deleted.curr,inserted.tar,deleted.tar
    INTO table3

    still seems to be missing comma also also it's not clear what is 'source = 0' ? Source.source? 

    Also explicitly specify table3 columns in the last portion of the command.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, May 01, 2014 11:08 PM
  • table3= action, text,efforts, newcurr, oldcurr, newtar,oldtar

    basically, if source=0 then i want to put 'APPzeroed' + source.aid

    if source<>0 then i want to put 'source value' +curr +'changed to'+tar end text,source.efforts,inserted.curr,deleted.curr,inserted.tar,deleted.tar INTO table3

    Thursday, May 01, 2014 11:23 PM
  • Which table the source column belongs to? Also, I don't understand your last sentence at all. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 02, 2014 12:20 AM
  • Check out the last example from MSDN document at this link:

    http://msdn.microsoft.com/en-us/library/bb510625.aspx

    Friday, May 02, 2014 1:18 AM
  • So you want one column named text that containse either 'AppInsert' + source.aid OR 'APPzeroed' + source.aid OR 'source value' +curr +'changed to'+tar?  If so, you don't want 3 CASE WHEN's, you want one CASE with 3 WHEN's.  So it would look like

    output $action
    case
    when $action='INSERT' THEN 'APPINSERT'+source.aid,
    when $action='UPDATE' and source=0 THEN 'APPzeroed' + source.aid
    when $action='UPDATE' THEN 'source value' +curr +'changed to'+tar 
    end text, source.efforts, inserted.curr, deleted.curr, inserted.tar, deleted.tar
    INTO table3

    Tom


    Friday, May 02, 2014 1:32 AM
  • Yes your right. I want to inser values under "text" field and tried that too and also removed last when and put else but no luck.  When I comment last update lines then it is working.
    • Edited by kdinuk Friday, May 02, 2014 2:58 AM
    Friday, May 02, 2014 2:43 AM
  • It's hard to say too much from what you have given us.  You have not given us table definitions or data and the code you show has syntax errors in it (for example, you reference source.ait in your ON condition, but there is no column named ait in your source derived table.

    One guess I have is that one or more of the columns you are concatenating to create the text column in the third case is NULL.  If that is the case, the whole expression is NULL.  You would need to use ISNULL() or COALESCE() functions (or if you are on SQL 2012 or later you could use the CONCAT() function).

    If that's not the case, I would recommend you give some code that we can run that demonstrates your problem.  For example, something like the following code.  The following code will show a procedure that uses a merge statement to work somewhat like what you have shown us, and is able to output all three cases.

    use tempdb
    go
    Create Table #cip(nid varchar(5), id varchar(5), efforts varchar(5), curr varchar(5), tar varchar(5));
    Create Table #cas(nid varchar(5), l_id varchar(5));
    Create Table #t1(caseid varchar(5), aid varchar(5), modi datetime, curr varchar(5), tar varchar(5));
    Create Table #table3(action char(6), text varchar(150), efforts varchar(5), currinserted varchar(5), currdeleted varchar(5), tarinserted varchar(5), tardeleted varchar(5));
    go
    
    Create Procedure FooProc(@caseid int, @nid int) As
    BEGIN
    MERGE #t1 AS target
    USING (SELECT a.id As aid,a.efforts,b.l_id, a.curr, a.tar FROM #cip a INNER JOIN #cas b ON a.nid=b.nid WHERE a.nid=@nid) AS source
    ON (target.caseid=@caseid AND target.aid=source.aid)
    WHEN MATCHED/* AND EXISTS
    (SELECT source.efforts, target.curr
    EXCEPT
    SELECT target.curr,target.tar) AND source.l_id IN (2,3) */
    THEN
    UPDATE SET target.curr=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    		   target.tar=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    			target.modi=CASE WHEN source.efforts=0 THEN GETDATE()
    							WHEN SOURCE.efforts<>0 THEN GETDATE() END
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT (caseid,aid,modi)
            VALUES (@caseid,IsNull(source.aid, '1'),getdate())
    output $action,
    case
    when $action='INSERT' THEN 'APPINSERT'+IsNull(source.aid,'')
    when $action='UPDATE' and source.efforts=0 THEN 'APPzeroed' + IsNull(source.aid,'')
    when $action='UPDATE' THEN 'source value' + IsNull(source.curr,'') +'changed to'+ IsNull(source.tar,'') end text,source.efforts, inserted.curr, deleted.curr, inserted.tar, deleted.tar
    INTO #table3;
    END;
    go
    
    Insert #cip(nid, id, efforts, curr, tar) Values 
    (1, 1, 2, 4, 8);
    Insert #cas(nid, l_id) Values
    (1, 16)
    ,(2, 32);
    -- This should output 1st case
    Exec FooProc 1,1;
    -- Check
    select 'First case', * from #table3
    -- This should output 2nd case
    Delete From #table3;
    Update #cip Set efforts = 0;
    Exec FooProc 1,1;
    -- Check
    select 'Second case', * from #table3
    -- This should output 3rd case
    Delete From #table3;
    Update #cip Set efforts = 1;
    Exec FooProc 1,1;
    -- Check
    select 'Third case', * from #table3
    go
    Drop Procedure FooProc;
    go
    Drop Table #cas;
    go
    Drop Table #cip;
    go
    Drop Table #t1;
    go
    Drop Table #table3;
    Tom

     
    Friday, May 02, 2014 4:32 AM
  • Thanks a lot Tom. I tested one case and it went thru fine. I have to do more testing.

    One more question - Can I put something like this? If I do am getting an error - "subqueries not allowed in the OUTPUT clause" If not this way, how to handle this scenario?

    One more issue - when i put everything in a stored procedure am getting  an error "An explicit value for the identity column in table 'journal' an only be specified when a column list is used and IDENTITY_INSERT is ON". If I run all statements without stored procedure, am not getting this error.

    One more thing, I have to get 'APPINSERT' & 'APPzeroed' and other from a config table.
    output $action,
    case
    when $action='INSERT' THEN select replace(value,@@text,IsNull(source.aid,'')) from config where id=1
    when $action='UPDATE' and source.efforts=0 THEN  select replace(value,@@text,IsNull(source.aid,'')) from config where id=2
    when $action='UPDATE' THEN 
    select replace(value,@@text,IsNull(source.aid,'')) from config where id=3
    + select replace(value,@@text,IsNull(source.aid,'')) from config where id=4 end text,
    source.efforts, inserted.curr, deleted.curr, inserted.tar, deleted.tar
    INTO #table3;
    END;



    • Edited by kdinuk Friday, May 02, 2014 5:32 AM
    Friday, May 02, 2014 5:20 AM
  • It seems that a lot has happened while I was sleeping!

    Anyway, this:

    UPDATE SET
    target.cur = CASE WHEN soure.efforts=0 THEN SOURCE.efforts
                      WHEN SOURCE.efforts<>0 THEN SOURCE.efforts
                 END,
    target.tar = CASE WHEN soure.efforts=0 THEN SOURCE.efforts
                      WHEN SOURCE.efforts<>0 THEN SOURCE.efforts
                 END,
    target.mod = CASE WHEN soure.efforts=0 THEN GETDATE()
                  WHEN SOURCE.efforts<>0 THEN GETDATE()
                 END

    Seems like a complicated way of saying:

    UPDATE SET
    target.cur = source.efforts,
    target.tar = source.efforts,
    target.mod = getdate()

    One more question - Can I put something like this? If I do am getting an error - "subqueries not allowed in the OUTPUT clause" If not this way, how to handle this scenario?

    Rather than doing all the manipulations in the OUTPUT clause, you will need to do those later with a separate UPDATE against the temp table.

    One more issue - when i put everything in a stored procedure am getting  an error "An explicit value for the identity column in table 'journal' an only be specified when a column list is used and IDENTITY_INSERT is ON". If I run all statements without stored procedure, am not getting this error.

    So you are supplying explicit values for an IDENTITY column. Either don't do that. Or, if you need to supply those values, the column should not be an IDENTITY column.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 02, 2014 9:39 AM
  • I have modifed TOM code. I want to insert values into table3 only if it is existing case id and do not want to inserted if it is 1st time created. As per my knowledge output clause will write record mandatory to table3, which i don't want if it is inserted first time. If the case already existing then it can be inserted into table3. I attached code...how do I control output clause..I have added in output clause @ecaseid>-1 . When I do this, am getting null values for text field it means it is controlling but dont want those records to be displayed. Should I delete after insertion in table3?
    use tempdb
    go
    Create Table #cip(nid varchar(5), id varchar(5), efforts varchar(5), curr varchar(5), tar varchar(5));
    Create Table #cas(nid varchar(5), l_id varchar(5));
    Create Table #t1(caseid varchar(5), aid varchar(5), modi datetime, curr varchar(5), tar varchar(5));
    Create Table #table3(action char(6), text varchar(150), efforts varchar(5), currinserted varchar(5), currdeleted varchar(5), tarinserted varchar(5), tardeleted varchar(5));
    go
    
    
    WHILE @@FETCH_STATUS=0
    BEGIN
    	SET @ecaseid=-1;
    	SET @ncaseid=-1;
    	SET @casetoupd=-1
    	SELECT TOP 1 @ecaseid=caseid FROM t1 
    BEGIN
    IF ((@ecaseid = -1) OR ((@ecaseid >-1)
    BEGIN	
    	INSERT INTO #t1
    	VALUES(..)
    	SET @ncaseid=(SELECT SCOPE_IDENTITY())
    	        ( caseid, aid, modi, curr, tar )
    	VALUES  ( '')
    IF @ncaseid>-1 THEN
    SET @casetoupd=@ncaseid
    ELSE
    IF @ecaseid >-1)
    SET @casetoupd=@ecaseid
    IF @casetoupd > -1
    BEGIN
    MERGE #t1 AS target
    USING (SELECT a.id As aid,a.efforts,b.l_id, a.curr, a.tar FROM #cip a INNER JOIN #cas b ON a.nid=b.nid WHERE a.nid=@nid) AS source
    ON (target.caseid=@caseid AND target.aid=source.aid)
    WHEN MATCHED/* AND EXISTS
    (SELECT source.efforts, target.curr
    EXCEPT
    SELECT target.curr,target.tar) AND source.l_id IN (2,3) */
    THEN
    UPDATE SET target.curr=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    		   target.tar=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    			target.modi=CASE WHEN source.efforts=0 THEN GETDATE()
    							WHEN SOURCE.efforts<>0 THEN GETDATE() END
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT (caseid,aid,modi)
            VALUES (@caseid,IsNull(source.aid, '1'),getdate())
    output $action,
    case
    when $action='INSERT' and @ecaseid>-1 THEN 'APPINSERT'+IsNull(source.aid,'')
    when $action='UPDATE' and @ecaseid>-1 source.efforts=0 @ecaseid>-1  THEN 'APPzeroed' + IsNull(source.aid,'')
    when $action='UPDATE' and @ecaseid>-1 THEN 'source value' + IsNull(source.curr,'') +'changed to'+ IsNull(source.tar,'') end text,source.efforts, inserted.curr, deleted.curr, inserted.tar, deleted.tar
    INTO #table3;
    END
    END;
    go
    
    Insert #cip(nid, id, efforts, curr, tar) Values 
    (1, 1, 2, 4, 8);
    Insert #cas(nid, l_id) Values
    (1, 16)
    ,(2, 32);
    -- This should output 1st case
    Exec FooProc 1,1;
    -- Check
    select 'First case', * from #table3
    -- This should output 2nd case
    Delete From #table3;
    Update #cip Set efforts = 0;
    Exec FooProc 1,1;
    -- Check
    select 'Second case', * from #table3
    -- This should output 3rd case
    Delete From #table3;
    Update #cip Set efforts = 1;
    Exec FooProc 1,1;
    -- Check
    select 'Third case', * from #table3
    go
    Drop Procedure FooProc;
    go
    Drop Table #cas;
    go
    Drop Table #cip;
    go
    Drop Table #t1;
    go
    Drop Table #table3;



    • Edited by kdinuk Friday, May 02, 2014 5:46 PM
    Friday, May 02, 2014 5:30 PM
  • Use Composable DML, e.g.

    INSERT INTO #Table3 (col1, col2, ...)
    SELECT * FROM (MERGE #t1 AS target
    USING (SELECT a.id As aid,a.efforts,b.l_id, a.curr, a.tar FROM #cip a INNER JOIN #cas b ON a.nid=b.nid WHERE a.nid=@nid) AS source
    ON (target.caseid=@caseid AND target.aid=source.aid)
    WHEN MATCHED/* AND EXISTS
    (SELECT source.efforts, target.curr
    EXCEPT
    SELECT target.curr,target.tar) AND source.l_id IN (2,3) */
    THEN
    UPDATE SET target.curr=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    		   target.tar=CASE WHEN source.efforts=0 THEN SOURCE.efforts
    							WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
    			target.modi=CASE WHEN source.efforts=0 THEN GETDATE()
    							WHEN SOURCE.efforts<>0 THEN GETDATE() END
    WHEN NOT MATCHED BY TARGET
    THEN
    INSERT (caseid,aid,modi)
            VALUES (@caseid,IsNull(source.aid, '1'),getdate())
    output $action,
    case
    when $action='INSERT' THEN 'APPINSERT'+IsNull(source.aid,'')
    when $action='UPDATE' and source.efforts=0 THEN 'APPzeroed' + IsNull(source.aid,'')
    when $action='UPDATE' THEN 'source value' + IsNull(source.curr,'') +'changed to'+ IsNull(source.tar,'') end text,source.efforts, inserted.curr, deleted.curr, inserted.tar, deleted.tar) X WHERE $Action = 'UPDATE'

    This is the idea.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, May 02, 2014 5:34 PM
  • It's not simple to follow what you are doing, because you are more or less are thinking aloud.

    The code you posted is also very funky, because you have

    WHILE @@FETCH_STATUS=0

    as if you had a cursor, but there is no FETCH statement in the loop.

         SELECT TOP 1 @ecaseid=caseid FROM t1

    Here is no ORDER BY or any condition that you want a processed row. Here it says t1, which may be a different table from #1 which you are merging to, and adding rows to (but not deleting from).

    Then again, do you need a loop at all? I don't think so.

         INSERT INTO #t1
         VALUES(..)
         SET @ncaseid=(SELECT SCOPE_IDENTITY())
                 ( caseid, aid, modi, curr, tar )
         VALUES  ( '')

    This also looks funny, because this is illegal syntax.

    Maybe the code makes sense to you, because you have abbreviated some parts for the posting, but for us poor souls who try to help you it is very difficult to get a grip.

    As per my knowledge output clause will write record mandatory to table3, which i don't want if it is inserted first time. If the case already existing then it can be inserted into table3. I attached code...how do I control output clause..

    I don't know what this means, but the OUTPUT clause alone will always insert a row. If you only want to insert a row under some conditions, use composable DML Naomi suggested and I also did earlier. Or insert everything into the temp table and delete the extra later. (This is of course less efficient, but the code may be easier to read. There is a certain mumbo-jumbo level over composable DML.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 02, 2014 6:34 PM
  • Thanks Erland and sorry for the confusion. You are absolutely right, I have modified now. Even I thought same to use temp table and insert & delete. Let me give a try.

    I have a question - how do i find out the caseid existing or not? Can i put something like this...It's not working though...need help pls

    if @ncaseid=-1 begin
    delete from table3 where caseid=@ncaseid
    end

    use tempdb go Create Table #ip(nid varchar(5), aid varchar(5), efforts varchar(5)); Create Table #cas(nid varchar(5), modi datetime,l_id varchar(5)); Create Table #t1(caseid varchar(5), aid varchar(5), modi datetime, curr varchar(5), tar varchar(5)); Create Table #table3(action char(6), text varchar(150), efforts varchar(5), currinserted varchar(5), currdeleted varchar(5), tarinserted varchar(5), tardeleted varchar(5)); go DECLARE db_cursor CURSOR FOR SELECT DISTINCT FROM #ip OPEN db_cursor FETCH NEXT FROM db_cursor INTO @nid BEGIN WHILE @@FETCH_STATUS=0 BEGIN SET @ecaseid=-1; SET @ncaseid=-1; SET @casetoupd=-1 SELECT TOP 1 @ecaseid=caseid FROM #ip ORDER BY modi BEGIN IF (@ecaseid = -1) OR ((@ecaseid >-1) AND L_id in (2,3)) --where 3 is complete BEGIN INSERT INTO #cas--case id will autogenerate and use can update l_id from UI ( nid, modi, l_id ) VALUES ( @nid,GETDATE(),1) --  need to check is this right? if it a new case or replaced
        INSERT INTO #table3
                ( action ,text ,efforts ,currinserted ,currdeleted ,tarinserted ,tardeleted )
        VALUES  ( 'insert', CASE WHEN caseid =@ncaseid THEN 'brand new' ELSE 'old'+CASid+'replaced'+WITH+@ncaseid text
                  '' ,'' ,'' ,'' ,'' ,''  ) SET @ncaseid=(SELECT SCOPE_IDENTITY()) IF @ncaseid>-1 THEN SET @casetoupd=@ncaseid ELSE IF @ecaseid >-1) SET @casetoupd=@ecaseid IF @casetoupd > -1 BEGIN MERGE #t1 AS target USING (SELECT a.id As aid,a.efforts,b.l_id, a.curr, a.tar FROM #ip a INNER JOIN #cas b ON a.nid=b.nid WHERE a.nid=@nid) AS source ON (target.caseid=@caseid AND target.aid=source.aid) WHEN MATCHED/* AND EXISTS (SELECT source.efforts, target.curr EXCEPT SELECT target.curr,target.tar) AND source.l_id IN (2,3) */ THEN UPDATE SET target.curr=CASE WHEN source.efforts=0 THEN SOURCE.efforts WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END, target.tar=CASE WHEN source.efforts=0 THEN SOURCE.efforts WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END, target.modi=CASE WHEN source.efforts=0 THEN GETDATE() WHEN SOURCE.efforts<>0 THEN GETDATE() END WHEN NOT MATCHED BY TARGET THEN INSERT (caseid,aid,modi) VALUES (@caseid,IsNull(source.aid, '1'),getdate()) output $action, case when $action='INSERT' THEN 'APPINSERT'+IsNull(source.aid,'') when $action='UPDATE' and source.efforts=0 THEN 'APPzeroed' + IsNull(source.aid,'') when $action='UPDATE' THEN 'source value' + IsNull(source.curr,'') +'changed to'+ IsNull(source.tar,'') end text,source.efforts, inserted.curr, deleted.curr, inserted.tar, deleted.tar INTO #table3; END END; go



    Thanks to everyone who supported me.






    • Edited by kdinuk Saturday, May 03, 2014 5:56 AM
    Saturday, May 03, 2014 1:58 AM
  • I have a question - how do i find out the caseid existing or not?

    So what is this jazz about the caseid existing or not? I have seen a lot of code in this thread, but I might have read the text around it poorly, because I don't understand what you are trying to achieve. Maybe you should start over from the beginning and telling us what your misson from start to end.

    But some comments on the code you posted:

    DECLARE db_cursor CURSOR FOR SELECT DISTINCT FROM #ip
    OPEN db_cursor
    FETCH NEXT FROM db_cursor
    INTO @nid
    BEGIN
    WHILE @@FETCH_STATUS=0
    BEGIN
         SET @ecaseid=-1;
         SET @ncaseid=-1;
         SET @casetoupd=-1
         SELECT TOP 1 @ecaseid=caseid FROM #ip ORDER BY modi

    First you run a cursor over #ip, and the you do a SELECT TOP 1 from it?

    And there is no FETCH and the end of the cursor loop?

    THEN
    UPDATE SET target.curr=CASE WHEN source.efforts=0 THEN SOURCE.efforts
                                       WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
                 target.tar=CASE WHEN source.efforts=0 THEN SOURCE.efforts
                                       WHEN SOURCE.efforts<>0 THEN SOURCE.efforts END,
                   target.modi=CASE WHEN source.efforts=0 THEN GETDATE()
                                       WHEN SOURCE.efforts<>0 THEN GETDATE() END

    I have already pointed this out once. These CASE expressions appears to completely pointless. No matter the value of Source.efforts, you will set the target columns to the same value. (Or can source.efforts be NULL?)

    And then there are things like:

    IF (@ecaseid = -1) OR ((@ecaseid >-1) AND L_id  in (2,3))

    L_id is not a varible and there is no column, so this is just incorrect SQL.

    IF @ecaseid >-1)

    And the right parenthesis is just incorrect syntax.

    It can be difficult to understand what people are trying to achieve from code that actually compiles and runs but does not produce the correct result. Here we are supposed to understand what you are trying to do from code that is not close to compile.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 03, 2014 10:21 AM