如何在单个查询中插入和更新。 [英] How Do I Do Insert And Update In A Single Query.

问题描述

我想将一些记录从源表移动到目标表。（从源表中移动插入到目标表中删除）在一个语句中。我正在阅读有关合并语句的内容但是它运行不正常。

  BEGIN   TRAN ; 
  MERGE  目标  AS  T 
  USING  来源  AS  S 
  ON （T.EmployeeID = S.EmployeeID）
  WHEN   NOT   MATCHED   BY   TARGET  
 那么  INSERT （EmployeeID，EmployeeName） VALUES （S.EmployeeID，S.EmployeeName）
  WHEN   MATCHED  
 那么  DELETE ; 
   -    OUTPUT $ action，Inserted。*，Deleted。*;  
  ROLLBACK   TRAN ; 

没有插入任何内容目标表。

解决方案

action，Inserted。*，Deleted。*;
ROLLBACK TRAN ;

没有任何内容插入目标表。

< blockquote>如果你回滚你的交易，你期望如何插入一些！你可能想结束它...

无论如何你最好使用单一形式的DELETE ... OUTPUT ... INTO，它已经作为单个事务工作，更容易编写，更容易阅读...

http://msdn.microsoft.com/en- us / library / ms177564.aspx [ ^ ]

您似乎正在尝试学习合并声明。请查看下面包含类似示例的链接。



使用MERGE插入，更新和删除数据 [ ^ ]



SQL Server 2008中的合并语句 [ ^ ]

Hi,
I want to move few records from a source table to destination table.(Move-insert into destination table delete from source table) in a single statement.I am reading about merge statement but it is not working properly.

BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET 
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN DELETE;
--OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;

Nothing is inserted to target table.

解决方案

action, Inserted.*, Deleted.*; ROLLBACK TRAN;

Nothing is inserted to target table.

How do you expect to insert some if you ROLLBACK your transaction! You probably meant to END it...
In anyway you better use the single form of DELETE ... OUTPUT ... INTO, it works already as a single transaction, easier to write and easier to read...
http://msdn.microsoft.com/en-us/library/ms177564.aspx[^]

It seems that you are trying to learn merge statement. Check below link that contain similar examples.

Inserting, Updating, and Deleting Data by Using MERGE[^]

Merge Statement in SQL Server 2008[^]

这篇关于如何在单个查询中插入和更新。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！