【SqlServer】解析INSERT，DELETE 和 UPDATE语句中的OUTPUT从句

sql server 支持在INSERT, DELTE, UPDATE后使用output [into]从句，output是直接输出到结果中，如果想要改变输出终端，比如想要输出到临时表中，这时候就需要使用 output into, 并且在into后指定要输出到的临时表。

INSERT OUTPUT

我们常常会遇见往一个表中插入数据，另一个表中需要插入的数据依赖于我们之前插入的数据。

比如：

学生和班级

--班级
declare @classroom table(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [classname] nvarchar(20) not null
);

--学生
declare @student table(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [classid] [int] not null,
    [studentname] nvarchar(20) not null
);

现在需要添加一个班级，并且在这个班级上添加两个学生。

 

student表中有一个classid字段，表示该学生的所在班级的id。classroom表也就是班级表的id是自增长的，那么怎么获得这个班级id呢？我们可以先给classroom表中插入数据，然后查询最大的id，就可以获得我们刚刚插入进去数据的id。

--1.先往班级表中插入数据
insert into classroom(classname) values('5班');
GO

--2.获得刚刚插入的id
declare @classid int
select @classid = max(id) from classroom;

--3.往学生表中插入数据
insert into student(classid,studentname) values(@classid,'张三');
GO

这样会有一个问题，就是你获得的classid不一定是你想要的id， 万一在你执行第二条sql语句之前，又有一个班级的插入操作，那么数据就不正确了。 

 

这时候可以使用Insert output into语句。

insert classroom
    output 
        INSERTED.id,'张三'
    into 
        student(classid,studentname)
values('5班');

 INSERTED  就是你插入到classroom表的那条数据， INSERTED.id  就是获得你插入的数据生成的id.

 

insert output into 只能插入一条数据，比如你想在添加了一个班级后，再在这个班级下添加两个学生，那么insert output into直接往student里插入数据就不正确了。但可以先把id保存下来，后面再拿着这个保存下来的id进行其他的操作。

--定义一个临时表，用于保存id
declare @temp table(id int)

--将插入生成的classid，保存到@temp临时表中。
insert @classroom
    output inserted.id
    into @temp(id)
values('5班');

--把classid从临时表@temp中取出来
declare @classid int;
select top 1 @classid = id from @temp;

insert into @student values(@classid,'张三');
insert into @student values(@classid,'李四');

select * from @classroom;
select * from @student;

也可以用@@identity，来实现这个功能。

--插入班级数据
insert @classroom values('5班');
insert @classroom values('6班');
insert @classroom values('7班');

--把classid 保存下来
declare @classid int;
select @classid = @@IDENTITY;

--往学生表中插入数据
insert into @student values(@classid,'张三');
insert into @student values(@classid,'李四');

select * from @classroom;
select * from @student;

因为@@identity只能获取主键值，而INSERT OUTPUT可以获取插了的那条数据的完整相关数据。比如，student表还依赖于classroom表的其他自动生成的字段（非主键字段），那么这时候就要用INSERT OUTPUT。

 

update output

 

update output into可以使用 INSERTED 也可以使用 DELETED ，INSERTED代表改变之后的数据，DELETED代表改变之前的数据。

update @student
set studentname = '王五'
output INSERTED.*,DELETED.*

因为有DELETED,所以我们可以给任何改变的数据，进行日志记录。

--创建student日志表
declare @student_log table(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [classid] [int] not null,
    [studentname] nvarchar(20) not null,
    [action] nvarchar(30) null
);
--每次update后都需要把改变之前的数据，记录到日志表中
update @student
set studentname = '王五'
output deleted.classid,deleted.studentname,'update'
into @student_log(classid,studentname,action)

select * from @student_log;

触发器也有INSERTED,UPLDATED，触发器和OUTPUT之间有一定的相通性。

 

delete output

 

delete output into和update比较相似。只不过它就没有INSERTED的了。因为是删除操作，所以只有deleted变量，有DELTED变量我们也可以对任何删除操作进行日志记录。

--创建student日志表
declare @student_log table(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [classid] [int] not null,
    [studentname] nvarchar(20) not null,
    [action] nvarchar(30) null
);
--每次delete后都把删除掉的数据，记录到日志中
delete from @student
output deleted.classid,deleted.studentname,'delete'
into @student_log(classid,studentname,action)
where id = 1;

select * from @student_log;