在sql server中插入更新数据帮助

c0vxltue 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(235)

我有一个insert-update过程，从2个过程中获取数据，并将数据放入表中。
所以程序1有这样的数据

AgentName   Tickets Closed
----------------------------
Agent1        10
Agent2         2
Agent3         4
Agent4         6
Agent5         9
Agent6        12

程序2有这样的数据

AgentName   Active Tickets
--------------------------
Agent1           2
Agent9           3
Agent10          1
Agent2           0
Agent3           0
Agent11          1

因此，现在在我的insert update过程中，我只从这两个过程中获取目标表中的匹配数据。
但我也需要得到不匹配的数据。
以下是我的程序1代码：

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

以下是我的程序2：

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

sql sql-server

来源：https://stackoverflow.com/questions/63197725/insert-update-data-help-in-sql-server

2条答案

按热度按时间

cpjpxq1n1#

您需要从表table1、@ticketcompleted、@ticketsactiv中获取不匹配的数据

SELECT * FROM
Table1 eps FULL JOIN 
@TicketsCompleted ptc
On eps.date1=ptc.Date1
and eps.Agent=ptc.Agent
Where eps.date1 is null 
or ptc.Date1 is null 
Or eps.Agent is null 
or ptc.Agent is null

    SELECT * FROM
    Table1 eps FULL JOIN 
    @TicketsActive ptc
    On eps.date1=ptc.Date1
    and eps.Agent=ptc.Agent
    Where eps.date1 is null 
    or ptc.Date1 is null 
    Or eps.Agent is null 
    or ptc.Agent is null

赞(0）回复(0）举报 2021-07-26

dauxcl2d2#

如果我理解正确的话，我可以建议你使用下面两种方法中的一种。
sql not和in子句：

...
SELECT COLUMNS_YOU_WANT FROM 
(
   QUERY_FOR_SOME_FILTERING
   WHERE YOUR_UNIQUE_COL NOT IN 
   (
      QUERY_FOR_SOME_FILTERING
   )
)

sql EXPEPT子句：

SELECT COL1, COL2, etc. 
FROM 
    QUERY_FOR_SOME_FILTERING
EXCEPT 
(
    SELECT COL1, COL2, etc. FROM 
    QUERY_FOR_SOME_FILTERING
)

赞(0）回复(0）举报 2021-07-26

我来回答

在sql server中插入更新数据帮助

2条答案

相关问题

热门标签

最新问答