SQL2005 modified data is returned

2011-01-06  来源:本站原创  分类:Database  人气:67 

Today, my colleagues in the company that wrote the new features of SQL2005 article, I feel very practical, here and share with you. The original copyright does not belong to me.

This technique is mainly used in the inserted and deleted virtual table, which I believe we are familiar with two tables. We previously used mainly in the trigger.

Let's review the following two tables:

Inserted and Deleted the two tables are logical tables and two table is the system managed , and stored in memory, not stored in the database , and therefore, does not allow users to modify it directly.
The structure of these two tables and the table is the role of the trigger at the same table structure. This is a dynamic two tables reside in memory, when the trigger is completed, they are deleted.

Next, look at our case, in fact, very simple to use, but also very practical. His addition to using the above two tables, but also used the output parameter.

Create table:

Code
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N '[TestTB]') AND type in (N'U '))
DROP TABLE TestTB
CREATE TABLE TestTB (
[Id] [int] IDENTITY (1,1) PRIMARY KEY NOT NULL,
Province [varchar] (50) NULL,
City [varchar] (50) NULL
)

1, insert the data, and return the inserted data:

INSERT INTO TestTB (Province, City)
output inserted.Province, inserted.City
VALUES ('Guangdong', 'Shenzhen')

Back to results:

SQL2005 modified data is returned

2 Similarly, deleting the data is the same, but nothing more is to use the deleted table.

delete from TestTB
output deleted .*
where

Back to results:

SQL2005 modified data is returned

3, the two combined together: Back before the update and updated data:

UPDATE TestTB SET Province = 'Hunan', City = 'Chenzhou'
OUTPUT 'I come from (before the update)' + DELETED.Province + DELETED.City as [Before], 'I'm from the (updated)' + Inserted.Province + Inserted.City as [After]
WHERE

Back to results:

SQL2005 modified data is returned

4, the returned results can also be stored in the table variable, which in the delete data, and to delete the data inserted into the history table is very useful

DECLARE @ tempTable TABLE (
id int,
Province varchar (50),
City varchar (50)
)
DELETE FROM TestTB OUTPUT deleted .* INTO @ tempTable
WHERE id> 4

SELECT * FROM @ tempTable

相关文章
  • SQL2005 modified data is returned 2011-01-06

    Today, my colleagues in the company that wrote the new features of SQL2005 article, I feel very practical, here and share with you. The original copyright does not belong to me. This technique is mainly used in the inserted and deleted virtual table,

  • SQL2005 return the modified data 2011-01-06

    Today, the company saw colleagues wrote a new SQL2005 features articles that are practical, here and share with you. Humans do not belong to me. This technique is mainly used in the inserted and deleted virtual tables, two tables, which I believe we

  • jquery autocomplete dwr combination of modified Data 2010-09-18

    Examples: Data is found in other places ^ _ ^ <script type="text/javascript" src="jquery.js"></script> <script type='text/javascript' src='jquery.autocomplete.js'></script> <link rel="stylesheet" ty

  • SQL2005 and (Excel | access) data import and export between 2011-05-23

    Source: http://liomao.iteye.com/blog/432875 -- Using SQL statements to realize sql2005 And data importing between Excel data export , Found on the Internet to one-under- , Implementation is the : --Excel---->SQL2005 Import : select * into useinfo fro

  • SQL2005 and (Excel | access) import and export data between 2011-05-23

    Source: http://liomao.iteye.com/blog/432875 -- Use SQL statements achieve sql2005 And data importing between Excel data export , Found on the Internet – with , Method like this : --Excel---->SQL2005 Import : select * into useinfo from OPENROWSET('MIC

  • SQL2005 and lock in the transaction 2011-08-17

    Source: http://blog.csdn.net/happyflystone Finally settled down to write an article for this transaction and lock, only to find really difficult to write to write, transactions, affairs and lock this topic too complex, and even some also touched (tha

  • Ext.data.Store Xiangjie (Reprinted) 2010-07-15

    [Size = medium] Ext.data.Store is the EXT in the data used for data exchange and interaction standards for middleware, whether or Grid ComboBox, is to read through it for data, type conversions, such as sorting and search page operation. Ext.data.Sto

  • SQL2005 installation experience 2010-10-14

    SQL2005 in five versions, as listed below, 1.Enterprise (Enterprise Edition), 2.Development (Developer Edition), 3.Workgroup, (Working Group Edition) 4.Standard, (Standard Edition) 5.Express. (Ah, the estimated version of it and call it simple) These

  • ExtJs of Ext.data.Store 2010-12-17

    ExtJs of Ext.data.Store Because the last used Ext.data.Store, feel very important, it reprinted an article http://blog.csdn.net/davidxj/archive/2009/04/23/4103647.aspx The basic usage Ext.data.Store Before use, the first instance to create a Ext.data

  • [Android2D game development Thirteen] (save game data [below]) Detailed SQLite storage, and the SQLite database file is stored in the SD card!!! 2011-01-04

    Shoes with you on a SharedPreference and File stream introduced how to store data, and recommended FileOutputStream / FileInputStream to store game data we, then this article is to introduce another as data storage for the way the game: SQLite light

  • ORACLE manipulate data & & & & transaction lock 2011-07-13

    INSERT statement INSERT statement syntax used to add new rows to the table INSERT INTO table [(column [, column ...])] VALUES (value [, value ...]); Use this syntax can only insert a row Note: This statement with the VALUES clause to add rows to the

  • Servlet accept the HTTP request parsing XML data, return XML 2008-05-23

    Servlet to accept submission of project needs over the XML parsing http, the return data. OutputStream output = null; HttpURLConnection conn = null; Document document = null; SAXReader reader = new SAXReader(); URL _url; if (StringUtil.isNotEmpty(xml

  • Java TCP / IP Socket Programming Notes (4) - send and receive data 2009-02-03

    1.TCP/IP agreement calls for information must be in the block (chunk) to send and receive, while the block length must be a multiple of 8 bits, therefore, we can assume that TCP / IP protocol to transmit the message that byte sequence. How to send an

  • SQL2005 client to SQL2000 server connection problem 2010-06-04

    Your machine to install the database is SQL2005, but the server is SQL2000. How to connect to the local SQL2005 server SQL2000. If you do not change the database has been installed, the server must (SQL2000) to upgrade to SP4 can normally use. The fo

  • oracle query / subquery / copy tables and data 2010-06-16

    1, the connection query 1. In connection connections within the connection to meet the conditions for return all records. By default, the implementation of the connection query if the operator does not specify any connection, then these queries are a

  • Monitor serial port using rxtx package expansion data (integration) 2010-07-09

    JavaEye recently read some of the development on the use of RxTxComm.jar expansion pack serial monitoring of the code, just a friend for me to do a little software in this area, put someone else's code to integrate a bit, made a small Demo. Other pro

  • Oracle data dictionary concept collection 2010-09-02

    Oracle data dictionary concept collection oracle database system is a complex software system. If you do not understand the principles of its internal structure and relationships, we can not design and write high-quality application software system,

  • DBCP data source configuration, the data source does not use the time there Could not close JDBC Connection 2010-10-11

    Database connection pool, this number will always maintain a minIdle connection, the connection here is not used if there will be a long time off. Not using the database may be disconnected: 1. With the database itself, automatically disconnect the c

  • Linux kernel structure data packets and sends (Netfilter mode) 2010-11-13

    Transfer from: http://blog.chinaunix.net/u/33048/showart_2043789.html, of: Godbach First, the structure of data packets Analysis This does not detail how to construct data packets in the kernel, below, if necessary, in appropriate location for analys

  • extjs study notes (f - 2) grid in the preservation of data, add and delete 2010-12-21

    149 failure: function (response) { 150 Ext.Msg.alert ("warning", "data update failed, please try again later!"); 151} 152}); 153} 154 else { 155 Ext.Msg.alert ("warning", "No need to update the data!"); 156} 157} 15