`
xiaoer_1982
  • 浏览: 1871323 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

[学习SQL SERVER 2005系列]关于INSERT、UPDATE 或 DELETE的OUTPUT及OUTPUT...INTO...用法

SQL ServerSQLGo工作 
阅读更多
  1. [学习SQLSERVER2005系列]关于INSERT、UPDATE或DELETE的OUTPUT及OUTPUT...INTO...用法
  2. 　　　　　作者：Flystone,转载注明出处
  4. 在INSERT、UPDATE、DELETE语句中使用OUTPUT得到语句影响的每行信息，今天我们来学习这个语法。
  5. 1、OUTPUT＿CLAUSE定义（语法参Transact-SQL语法约定）：
  7. <OUTPUT_CLAUSE>::=
  8. {
  9. [OUTPUT<dml_select_list>INTO{@table_variable|output_table}[(column_list)]]
  10. [OUTPUT<dml_select_list>]
  11. }
  12. <dml_select_list>::=
  13. {<column_name>|scalar_expression}[[AS]column_alias_identifier]
  14. [,...n]
  16. <column_name>::=
  17. {DELETED|INSERTED|from_table_name}.{*|column_name}
  19. 2、OUTPUT＿CLAUSE说明：
  20. 返回受INSERT、UPDATE或DELETE语句影响的每行的信息，或者返回基于上述每行的表达式。这些结果可以返回到处理应用程序，以供在确认消息、存档以及其他类似的应用程序要求中使用。此外，也可以将结果插入表或表变量。
  22. 3、典型应用：
  23. 1、根据当前表的数据有条件的生成历史或新的初始化数据；
  24. 2、把INSERT、UPDATE或DELETE语句影响的每行的信息暂存处理或反馈给应用程序完成业务或逻辑的完整性；
  25. 3、OUTPUT子句对于在INSERT或UPDATE操作之后检索标识列或计算列的值可能非常有用；
  26. 4、示列：
  27. 1、根据当前表的数据有条件的生成历史数据；
  28. 记得我以前做零售及水厂应用系统时都会有一个月未数据的处理功能，无非根据当前的数据自动生成下一个月数据的初始值。下面我以一个简化了例子来说明，例子是记录员工每个月工分变化中，我们以12月的数据生成下个月的月初数据。
  29. ------------------------------------
  30. --Author:happyflsytone
  31. --Date:2008-10-0216:39:39
  32. --Description:根据当前数据生成下个月的月初数据,并删除历史数据
  33. ------------------------------------
  34. DECLARE@sTABLE([年]INT,[月]INT,[工号]INT,[上月工分值]INT,[本月工分值]INT);
  36. INSERT@sSELECT2008,12,1,10,11;
  37. INSERT@sSELECT2008,12,2,11,12;
  38. INSERT@sSELECT2008,12,3,11,13;
  39. INSERT@sSELECT2008,12,4,3,5;
  40. INSERT@sSELECT2008,12,5,1,7;
  41. INSERT@sSELECT2008,12,6,2,11;
  44. DELETEFROM@s
  45. OUTPUTCASEWHENDELETED.[月]=12THENDELETED.[年]+1ELSEDELETED.[年]END,
  46. CASEWHENDELETED.[月]=12THEN1ELSEDELETED.[月]+1END,
  47. DELETED.[工号],DELETED.[本月工分值],NULLas[上月工分值]
  48. INTO@s;
  49. SELECT*
  50. FROM@s
  51. ORDERBY1,2,3
  52. /*
  53. 年月工号上月工分值本月工分值
  54. -------------------------------------------------------
  55. 20091111NULL
  56. 20091212NULL
  57. 20091313NULL
  58. 2009145NULL
  59. 2009157NULL
  60. 20091611NULL
  62. (6行受影响)
  64. */
  67. 2、根据业务规则的需要保证数据完整性。
  68. 在这个例程里我假设在更新员工的最后登录时间同时增加一条日志信息。先看测试数据：
  69. ------------------------------------
  70. --Author:happyflsytone
  71. --Date:2008-10-0216:39:39
  72. --Description:员工登录时更新员工表的最后登录时间，同时在日志表增加一条登录信息
  73. ------------------------------------
  74. --操作员信息表(本例只关心最后登录时间，所以员工的信息不深入表述)
  75. DECLARE@PTABLE([工号]INT,[姓名]varchar(16),[最后登录时间]datetime);
  77. INSERT@PSELECT1,'test1',getdate()-1;
  78. INSERT@PSELECT2,'test2',getdate()-1;
  79. INSERT@PSELECT3,'test3',getdate()-1;
  80. INSERT@PSELECT4,'test4',getdate()-1;
  81. INSERT@PSELECT5,'test5',getdate()-1;
  82. INSERT@PSELECT6,'test6',getdate()-1;
  83. --操作员操作日志(象征性列举一些字段)
  84. DECLARE@LOGTABLE([工号]INT,[操作时间]DATETIME,[操作类型]CHAR(6),[操作说明]VARCHAR(200));
  86. --模拟工号为3的操作员登录，并记录相应日志
  87. UPDATE@p
  88. SET[最后登录时间]=GETDATE()
  89. OUTPUTDELETED.[工号],DELETED.[最后登录时间],'出舱','成功出舱行走，身体状况良好，仪器工作正常，请主席放心!'
  90. INTO@log
  91. WHERE[工号]=3;
  93. --查看日志
  95. SELECT*
  96. FROM@LOG;
  97. /*
  98. 工号操作时间操作类型操作说明
  99. ----------------------------------------------------------------------------
  100. 32008-10-0117:06:58.790出舱成功出舱行走，身体状况良好，仪器工作正常，请主席放心!
  101. (1行受影响)
  102. */
  104. 注：其实我们可以通过这个OUTPUT_CLAUSE向应用程序提供数据操作的历史信息，或是把数据缓存在表变量中以备程序再次调用，关于这方面的例子就不多说明，因为这是最基本的OUTPUT_CLAUSE应用。
  106. 3、标识列或计算列方面的应用
  107. 对于标识列我们可能通过@@IDENTITY、SCOPE_IDENTITY和IDENT_CURRENT几个相似的函数获得，他们都返回插入到表的IDENTITY列的最后一个值(本身这几个函数还是有差异的，主要是它们的作用域，请查联机帮助)。我们注意到它们只是返回最后一个值，对于批量时就无能无力了。对于实时并发多的系统时我们可以利用OUTPUT_CLAUSE语句把标识列的值提取出来。
  109. ------------------------------------
  110. --Author:happyflsytone
  111. --Date:2008-10-0216:39:39
  112. ------------------------------------
  113. CREATETABLEScrapReason(scrapreasonidINTIDENTITY,[name]VARCHAR(50),modifieddateDATETIME)
  114. ;
  115. --接受标识列值的表变量
  116. DECLARE@MyTableVarTABLE(ScrapReasonIDSMALLINT,
  117. NameVARCHAR(50),
  118. ModifiedDateDATETIME);
  119. --模拟插入数据
  120. INSERTScrapReason
  121. OUTPUTINSERTED.ScrapReasonID,INSERTED.Name,INSERTED.ModifiedDate
  122. INTO@MyTableVar
  123. SELECTN'OperatorIDENTITY',GETDATE()
  124. FROMsys.objects;
  126. --查看记录的标识列数据
  127. SELECTScrapReasonID,Name,ModifiedDateFROM@MyTableVar;
  129. GO
  130. droptableScrapReason;
  131. /*
  132. ScrapReasonIDNameModifiedDate
  133. --------------------------------------------------------------------------------------
  134. 1OperatorIDENTITY2008-10-0217:42:19.000
  135. 2OperatorIDENTITY2008-10-0217:42:19.000
  136. 3OperatorIDENTITY2008-10-0217:42:19.000
  137. 4OperatorIDENTITY2008-10-0217:42:19.000
  138. 5OperatorIDENTITY2008-10-0217:42:19.000
  139. 6OperatorIDENTITY2008-10-0217:42:19.000
  140. .....
  141. .....
  142. 62OperatorIDENTITY2008-10-0217:42:19.000
  143. 63OperatorIDENTITY2008-10-0217:42:19.000
  145. (63行受影响)
  147. */
  150. 下面我们再来看看触发器使用OUTPUT＿CLAUSE的情况，
  152. ------------------------------------
  153. --Author:happyflsytone
  154. --Date:2008-10-0216:39:39
  155. ------------------------------------
  156. CREATETABLETA(
  157. scrapreasonidINTIDENTITYPRIMARYKEY,
  158. [name]VARCHAR(50),
  159. modifieddateDATETIME
  160. )
  161. ;
  162. CREATETABLETB(
  163. IDINTREFERENCESTA(SCRAPREASONID),
  164. [name]VARCHAR(50),
  165. MODIFIEDDATEDATETIME
  166. );
  167. GO
  168. CREATETRIGGERTR_INSERT
  169. ONTA
  170. INSTEADOFINSERT
  171. AS
  172. BEGIN
  173. --接受标识列值的表变量
  174. DECLARE@MyTableVarTABLE(IDINT,
  175. [NAME]VARCHAR(10),
  176. ModifiedDateDATETIME);
  177. INSERTTA
  178. OUTPUTINSERTED.scrapreasonid,INSERTED.[NAME],INSERTED.ModifiedDate
  179. INTO@MyTableVar
  180. SELECT[name],modifieddateFROMINSERTED
  181. INSERTINTOTBSELECT*FROM@MyTableVar
  182. END
  183. GO
  185. --模拟插入数据
  186. INSERTTASELECT'TEST',GETDATE();
  187. INSERTTASELECT'TEST2',GETDATE();
  189. --查看记录的标识列数据
  190. SELECT*FROMTB;
  193. /*
  194. IDnameMODIFIEDDATE
  195. ------------------------------------------------------------------------------------
  196. 1TEST2008-10-0217:53:46.780
  197. 2TEST22008-10-0217:53:46.870
  199. (2行受影响)
  201. */
  202. DROPTABLETB,TA;
  205. 最后说明一下使用OUTPUT子句的注意事项：
  206. 以下语句中不支持OUTPUT子句：
  208. 1、引用本地分区视图、分布式分区视图或远程表的DML语句。
  209. 2、包含EXECUTE语句的INSERT语句。
  210. 3、不能将OUTPUTINTO子句插入视图或行集函数。
  211. 4、参数或变量作为UPDATE语句的一部分进行了修改，则OUTPUT子句将始终返回语句执行之前的参数或变量的值而不是已修改的值
  215. Flystone于常州
分享到：
| Oracle Select语句中Case语句和decode函数 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ORACLE和SQL Server的语法区别

    - **SQL Server:** SQL Server的INSERT语句同样允许插入单行或多行数据，并且可以使用`OUTPUT` 子句来捕获受影响的行。 ```sql INSERT INTO table_name (column1, column2, ...) OUTPUT inserted.column1, ...

    MSSQL2005 INSERT,UPDATE,DELETE 之OUTPUT子句使用实例

    在SQL Server 2005中，`OUTPUT`子句是一个强大的特性，它允许你在执行`INSERT`, `UPDATE`, 或 `DELETE`语句时捕获受影响的数据行。这个功能非常实用，因为它允许开发者实时跟踪数据库中的变化，而无需额外的查询来...

    SQL2005新特性研究报告-编程性第二部分\SQL2005新特性研究报告-第二部分\SQL2005新特性研究报告-第二部分.doc

    SQL Server 2005在编程性方面的一大进步体现在其引入了OUTPUT语句，该语句允许用户在执行INSERT、UPDATE或DELETE语句时返回受影响的每一行的信息或基于这些行的表达式。这一特性极大地增强了SQL Server的灵活性和...

    SQLServer2005 Output子句获取刚插入的ID值

    在SQL Server 2005中，`OUTPUT`子句是一个强大的特性，它允许你在执行INSERT、UPDATE或DELETE操作时捕获受影响的数据行。对于开发人员来说，这尤其有用，因为可以直接获取到新生成的ID值或其他列的更新值，而无需...

    SQLServer开发人员应聘经常问到的问题

    - **定义**：INSTEAD OF 触发器是一种特殊类型的触发器，当对触发它的表执行INSERT、UPDATE或DELETE操作时，INSTEAD OF 触发器会代替这些操作并执行自定义代码。 - **优势**：允许开发者实现更复杂的业务逻辑，提高...

    SQL Server Merger实现数据的同步

    在SQL Server 2008及更高版本中引入了`MERGE`语句，这一功能允许用户在一个语句中根据源表与目标表之间的联接结果来执行插入、更新或删除操作。具体来说，可以根据源表与目标表的匹配情况有条件地更新或插入数据行，...

    SQL server 语句大全

    根据给定的文件标题、描述、标签以及部分内容，下面将详细介绍相关的SQL Server知识点： ### SQL语法 #### 创建数据文件 创建数据文件时，通常需要指定数据文件的逻辑名称、物理名称（包括完整的路径和文件扩展名...

    SQL Server 2012 Tutorials - Transact SQL DML Reference

    在探讨《SQL Server 2012教程：Transact SQL DML参考》这一主题时，我们深入解析数据操作语言（Data Manipulation Language，简称DML）的诸多方面，该语言是用于检索、处理SQL Server 2012数据库中数据的关键工具。...

    Microsoft SQL Server 2008技术内幕：T-SQL查询(第二卷)

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度，以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

    SQL学习大全.pdf

    SQL语句的基本语法主要包括查询（SELECT）、更新（UPDATE）、插入（INSERT）和删除（DELETE）等操作。 - **普通SQL**：最常用的SQL语句类型之一，例如： ```sql SELECT * FROM tableName; ``` - **动态SQL**：当...

    SQL Server 2012 Transact-SQL DML Reference

    OUTPUT 子句用于返回由 INSERT、UPDATE 或 DELETE 语句操作的行的信息。 - **语法**： - `INSERT INTO table_name (column_list) OUTPUT inserted.column_name INTO output_table (column_list) VALUES (value_list...

    SQLServer2008技术内幕T-SQL查询包含源代码及附录A

    《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容，以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...

    获取SQL触发器的状态

    在SQL Server中，触发器是一种特殊类型的存储过程，它被设计为响应对特定表的数据修改（如INSERT、UPDATE或DELETE操作）而自动执行。触发器可以用来确保数据完整性，并且可以在记录被插入、更新或删除时执行复杂的...

    sql语句的基本介绍和用法

    在SQL中，有一些最常用的操作语句，如`SELECT`、`INSERT`、`UPDATE`、`DELETE`等。这些语句是学习SQL的基础。 #### 三、`SELECT`语句详解 `SELECT`语句是最常用的SQL语句之一，用于从数据库中检索数据。 ##### 1. ...

    微软内部资料-SQL性能优化3

     Analyze the output of blocking scripts and Microsoft® SQL Server™ Profiler to troubleshoot locking and blocking issues.  Formulate hypothesis to resolve locking and blocking issues. ...

    T-SQL 增强功能

    4. **OUTPUT子句**：在DML操作（如INSERT、UPDATE、DELETE）中，OUTPUT子句可以捕获受影响的行，以便进一步处理或分析。 5. **CASE表达式**：CASE表达式提供了条件逻辑，可以在查询中根据不同的条件返回不同的值，...

    SQL练习补充(对不同层次的sql,进行小测试,主要是对DML语句)

    - **TOP关键字**：用于限制查询结果的数量，也可用于限制INSERT或DELETE操作的影响行数。 - **OUTPUT子句**：在UPDATE和DELETE操作中捕获更改的数据。 - **IDENTITY列**：自动递增的列，常用于主键。 - **TRIGGER**...

    静态、动态sql及各种游标

    静态SQL是指在PL/SQL中直接运行的SQL语句，例如SELECT、INSERT、UPDATE、DELETE等语句。这种方式的优点是简单易用，缺点是灵活性不高，不能在运行时动态生成SQL语句的内容。 二、动态SQL 动态SQL是指利用EXECUTE ...

    sql_plus_command.rar_plus_sql

    使用`INSERT INTO`插入数据，`UPDATE`更新数据，`DELETE FROM`删除数据。 9. **索引和视图**： `CREATE INDEX`创建索引，提高查询性能；`CREATE VIEW`创建视图，提供虚拟表。 10. **事务管理**： `COMMIT`提交...

    SQL数据库语句.doc

    30. **Output 语句**: 在DML操作（INSERT, UPDATE, DELETE）中捕获受影响的行。 31. **自定义表类型**: 使用`CREATE TYPE`创建用户定义的表类型。 32. **执行字符串语句**: `EXEC`或`sp_executesql`允许执行动态...

Magicbox
Global site tag (gtag.js) - Google Analytics