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

最新推荐文章于 2023-07-13 15:48:21 发布
最新推荐文章于 2023-07-13 15:48:21 发布
阅读量185 收藏
点赞数
文章标签： SQL Server SQL Go 工作
  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于常州
iteye_18688
关注
Sql server 2005中output用法解析
weixin_30677475的博客
12-04 125
一、关于output子句 SQL Server 2005中的output子句，可以使你从修改语句（INSERT、UPDATE、DELETE）中将数据返回到表变量中。带结果的 DML 的有用方案包括清除和存档、消息处理应用程序以及其他方案。这一新的 OUTPUT 子句的语法为： OUTPUT INTO @table_variable 可以通过引用插入的表和删除的表来访问被修改的行的...
sql delete output
weixin_30894389的博客
05-16 118
select * into #student1 from student select * from #student1 create table #temp2( id int not null,name nvarchar(50))--drop table #temp2CREATE TABLE #tempCodes (PromotionsCode VARCHAR(50),CustomerId...
SQL Server里面如何导出包含(insert into)数据的SQL脚本 (转载ZZ)
gaozzsoft的专栏
01-27 162
 通常情况下，SQL Server里面的生成SQL脚本，只会包含数据库及表的字段结构，而不会包含表的数据，也就是SQL脚本里面只有Create database，Create table 这样的语句，没有insert into。 因为SQL Server并不包含这个功能，只能靠第三方的代码了。 以下存储过程可以实现： CREATE PROCEDURE dbo.Us...
Sqlserver 关于update output into从句的实践示例
lusklusklusk的博客
07-13 354
Sqlserver 关于update output into从句的实践示例
MSSQL2005 INSERT,UPDATE,DELETE 之OUTPUT子句使用实例
09-11
在SQL Server 2005中，`OUTPUT`子句是一个强大的特性，它允许你在执行`INSERT`, `UPDATE`, 或 `DELETE`语句时捕获受影响的数据行。这个功能非常实用，因为它允许开发者实时跟踪数据库中的变化，而无需额外的查询来...
SQLServer2005 Output子句获取刚插入的ID值
09-11
在SQL Server 2005中，`OUTPUT`子句是一个强大的特性，它允许你在执行INSERT、UPDATE或DELETE操作时捕获受影响的数据行。对于开发人员来说，这尤其有用，因为可以直接获取到新生成的ID值或其他列的更新值，而无需...
Hive配置和基本操作 (2).docx
10-30
Hive是Apache Hadoop生态系统中的一个数据仓库工具，它允许用户使用SQL-like语句（HQL，Hive Query Language）来查询、管理和处理存储在Hadoop分布式文件系统（HDFS）上的大规模数据集。在进行Hive操作之前，我们...
pl／sql个人笔记.
11-17
- **DML** (Data Manipulation Language)：包括INSERT（插入）、UPDATE（更新）、DELETE（删除），用于操作表中的数据。 - **事务控制语句**：COMMIT（提交）、ROLLBACK（回滚）、SAVEPOINT（保存点），用于管理事务...
For.Dummies.Oracle.PL.SQL.For.Dummies.Jun.2006.
04-17
根据提供的文件信息，我们可以推断出这是一本关于Oracle PL/SQL编程的指南书籍，书名为《Oracle PL/SQL For Dummies》，出版于2006年6月，作者是Michael Rosenblum和Dr. Paul Dorsey。下面将对书中可能涉及的一些...
带有OUTPUT的INSERT,DELETE,UPDATE
sage425的专栏
03-10 931
带有OUTPUT的INSERT,DELETE,UPDATE
SQL Server在Delete操作时候使用output [deleted].*
weixin_30279315的博客
07-22 716
delete from achievements output [deleted].* where TypeId=2 转载于:https://www.cnblogs.com/fanweixiao/archive/2010/08/10/1796870.html
mysql 删除语句_MySQL DELETE 语句
weixin_39905500的博客
01-18 1379
MySQL DELETE 语句你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。你可以在 mysql> 命令提示符或 PHP 脚本中执行该命令。语法以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：DELETEFROMtable_name[WHEREClause]如果没有指定 WHERE 子句，MySQL 表中的所有记...
sql server output
最新发布
12-20
在SQL Server中，OUTPUT功能用于将INSERT、UPDATE和DELETE操作的结果返回给用户。它可以返回受影响的行的数据，以便用户可以查看或进一步处理。 以下是使用OUTPUT功能的示例： 1. INSERT操作的OUTPUT： ```sql -- 创建一个示例表 CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(255), ContactName VARCHAR(255), Address VARCHAR(255) ); -- 插入数据并返回插入的行 INSERT INTO Customers (CustomerName, ContactName, Address) OUTPUT INSERTED.* VALUES ('John Doe', 'John', '123 Main St'); ``` 这将插入一行数据并返回插入的行的所有列。 2. UPDATE操作的OUTPUT： ```sql -- 更新数据并返回更新的行 UPDATE Customers SET Address = '456 Elm St' OUTPUT DELETED.*, INSERTED.* WHERE CustomerID = 1; ``` 这将更新CustomerID为1的行的Address列，并返回更新前和更新后的行的所有列。 3. DELETE操作的OUTPUT： ```sql -- 删除数据并返回删除的行 DELETE FROM Customers OUTPUT DELETED.* WHERE CustomerID = 1; ``` 这将删除CustomerID为1的行，并返回删除的行的所有列。 需要注意的是，OUTPUT功能在SQL Server 2005及更高版本中可用。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值