OUTPUT子句是SQL Server 2005 中对INSERT、UPDATE和DELETE新增的,今天看见园子里有人提起(SQL2005中返回修改后的数据),就在这里记录一下它的语法。
OUTPUT { DELETED
|
INSERTED
|
from_table_name }. {
*
|
column_name }
[
,...n
]
INTO { @table_variable | output_table }
INTO { @table_variable | output_table }
参数说明:
DELETED , INSERTED 和触发器里的一样,是系统的虚拟表,他们保存了以更新的原始和修改之后的值。
下面是一个完整的例子:
创建表:
CREATE
TABLE
product(
productId INT IDENTITY PRIMARY KEY ,
Name NVARCHAR ( 50 )
)
productId INT IDENTITY PRIMARY KEY ,
Name NVARCHAR ( 50 )
)
插入数据:
INSERT
INTO
product(
[
Name
]
)
VALUES
(
'
MP3
'
);
测试OUTPUT:
DECLARE
@ProductChanges
TABLE
(
DeletedName NVARCHAR ( 50 ),
InsertedName NVARCHAR ( 50 )
)
UPDATE product
SET Name = ' myname '
OUTPUT DELETED.Name,INSERTED.Name
INTO @ProductChanges
WHERE productId = 1
SELECT DeletedName,InsertedName
FROM @ProductChanges
DeletedName NVARCHAR ( 50 ),
InsertedName NVARCHAR ( 50 )
)
UPDATE product
SET Name = ' myname '
OUTPUT DELETED.Name,INSERTED.Name
INTO @ProductChanges
WHERE productId = 1
SELECT DeletedName,InsertedName
FROM @ProductChanges
执行结果:
DeletedName InsertedName
1 MP3 myname