MSSQL2005 Insert,update,delete's OUTPUT clause use instance _mssql2005

Copy Code code as follows:

-->title:generating test Data
-->author:wufeng4552
-->date:2009-10-07 15:16:26
If object_id (' Ta ') is not null drop table Ta
Go
CREATE TABLE TA (ID int identity,[name] varchar (10))
Insert Ta ([name]) SELECT ' A ' UNION ALL
Select ' B ' UNION ALL
Select ' C ' UNION ALL
Select ' d ' UNION ALL
Select ' E ' UNION ALL
Select ' F ' UNION ALL
Select ' G '
If object_id (' TB ') is not null drop table TB
Go
CREATE TABLE TB (ID int identity,[name] varchar (10))
Insert TB ([name]) SELECT ' A ' UNION ALL
Select ' B ' UNION ALL
Select ' C '
--insert statement to use OUTPUT into
Insert TB Output
Inserted.id,
Inserted. [Name]
SELECT [Name]
From Ta where don't exists (select 1 from TB where [name]=ta.[ Name])
/*
ID Name
----------- ----------
4 D
5 E
6 F
7 g
*/
--Delete the recording you just inserted
Delete TB where [name]> ' C '
--Save this result set to a table-valued variable
Declare @t table (ID int,[name] varchar (10))
Insert TB Output
Inserted.id,
Inserted. [Name]into @t
Select [Name] from TA where NOT exists (select 1 from TB where [name]=ta.[ Name])
SELECT * FROM @t
/*
ID Name
----------- ----------
8 D
9 E
Ten F
One G
(4 data columns are impacted)
*/
--delete statement using OUTPUT
Delete TB output deleted.* where id=9
/*
ID Name
----------- ----------
9 E
(1 data columns are impacted)
*/
--UPDATE statement using OUTPUT into
Update TB set [name]= ' Test ' output inserted.* where id=10
/*
ID Name
----------- ----------
Ten Test
(1 data columns are impacted)
*/
/*
The OUTPUT clause can be useful for retrieving the value of an identity column or computed column after an insert operation.
Alternatively, the output clause can be used in the update and DELETE statements, and the values are obtained from the Insert table or delete table, and they are returned.
The OUTPUT clause is not supported in the following statement:
L references a DML statement for a local partitioned view, distributed partitioned view, or remote table.
INSERT statement containing the EXECUTE statement.
L cannot insert an OUTPUT into clause into a view or rowset function.
Concise output clause, which simplifies the operation of importing data into SQL Server.