insert update help in sql server

  • Krish319

    Old Hand

    Points: 331

    Hello All,

    I have a insert update procedure where I am getting data from 2 procedures and putting the data in to a table.

    So procedure 1 has data like this

     

    AgentName

    Tickets Closed

    Agent1

    10

    Agent2

    2

    Agent3

    4

    Agent4

    6

    Agent5

    9

    Agent6

    12

    Procedure 2 has data like this

    AgentName

    Active Tickets

    Agent1

    2

    Agent9

    3

    Agent10

    1

    Agent2

    0

    Agent3

    0

    Agent11

    1

    So now in my insert update procedure I am getting only matching data into destination table from both the procedures.

    But I need to get unmatched data also.

    Below is my code

     

    -----------------Procedure 1 Data-----------------

    BEGIN

    insert into Table1

    (

    Date1,

    Agent,

    TicketsCompleted

    )

    exec Procedure1

    declare @TicketsCompleted table

    (

    Date1 datetime,

    TicketsCompleted int

    )

    insert into @TicketsCompleted

    exec Procedure1

    update Table1

    set TicketsCompleted= ptc.TicketsCompleted

    from Table1 eps , @TicketsCompleted ptc

    where eps.date1=ptc.Date1

    and eps.Agent=ptc.Agent

    -----------------Procedure 2 Data-----------------

    declare @TicketsActive table

    (

    Date1 datetime,

    TicketsActive int

    )

    insert into @TicketsActive

    exec Procedure2

    update Table1

    set TicketsActive= ptc.TicketsActive

    from Table1 eps , @TicketsActive ptc

    where eps.date1=ptc.Date1

    and eps.Agent=ptc.Agent

    Please help

    • This topic was modified 1 week, 4 days ago by  Krish319.
  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    Reading through your SP's something is wrong.  Your "Procedure 1 Data" calls "EXEC Procedure1" twice but puts the data into 2 different tables with 2 different sets of columns.  That's not gonna work.  You are also doing some logic that doesn't make sense as you are comparing columns that don't exist (ptc.Agent for example in Procedure2 Data).

    If I am understanding what youw ant to do correctly, you want to get the agent, the open tickets and the completed tickets grouped by date?  If so, dump the data from both stored procedures into a temp table (one for each), then do a full outer join on the data.  Something similar to:

    DECLARE @tes1 TABLE
    (
    [id] INT
    , [oticket] INT
    );
    DECLARE @tes2 TABLE
    (
    [id] INT
    , [cticket] INT
    );
    INSERT INTO @tes1
    (
    [id]
    , [oticket]
    )
    VALUES
    (
    0
    , 0
    )
    , (
    1
    , 1
    )
    , (
    2
    , 2
    );
    INSERT INTO @tes2
    (
    [id]
    , [cticket]
    )
    VALUES
    (
    0
    , 0
    )
    , (
    1
    , 1
    )
    , (
    3
    , 3
    );
    SELECT
    ISNULL( [main].[id]
    , [RJ].[id]
    )
    , ISNULL( [FOJ].[cticket]
    , 0
    )
    , ISNULL( [mainFULL].[oticket]
    , 0
    ) AS [oticket]
    FROM @tes1 AS [main]
    FULL OUTER JOIN @tes2 AS [FOJ]
    ON [FOJ].[id] = [main].[id];

     

    I think that is similar to what you are looking to do or am I way out to left field?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply