SQLServer 2005中DML的 Insert,Update,Delete 的新功能

时间：2011-03-10 来源：ChallengerDBA

学习MSSQLServer　将近六年时间，工作时用SQLServer数据库开发到现在管理数据库近三年。现在把自己认为最好分享给大家。希望能同大家一起交流和学习。有什么不对的地方还请多多指教。今天是第一次发博文，哈哈……

use tempdb 
go
if object_id(N'dbo.T') is not null
drop table dbo.t
go
create table T(id int identity,name varchar(30))

---
-- 以下测试均在MS SQL Server 2005 以上的版本中。
---
--insert  
declare @v_T table(id int,name varchar(30))
insert top (5) into t(name) output inserted.* into @v_t output inserted.*
SELECT TOP (5) name
    FROM (SELECT 'sp_msforeachdb' AS name
          UNION ALL
          SELECT 'sp_msforeachtable'
          UNION ALL
          SELECT 'sp_mointor'
          UNION ALL
          SELECT 'sp_spaceused'
          UNION ALL
          SELECT 'sp_updatestats'
          UNION ALL
          SELECT '@@max_connections'
          UNION ALL
          SELECT '@@pack_received'
          UNION ALL
          SELECT '@@pack_sent'
          UNION ALL
          SELECT '@@pack_errors') a
    ORDER BY name 
select * from @v_t  

--可以利用output inserted.id 来返回最近id,如果批量导入须要返回插入id 此方法可以考虑

INSERT INTO T SELECT SUSER_SNAME();SELECT SCOPE_IDENTITY() --返回插入到当前会话中当前作用域内的最后一个 IDENTITY 列值

INSERT INTO T SELECT USER_NAME();SELECT @@identity --返回插入到当前会话中任何作用域内的最后一个 IDENTITY 列值

INSERT INTO T SELECT SUSER_SNAME();SELECT IDENT_CURRENT('T') --不受作用域和会话的限制，而受限于指定的表。
 
--备注：一个作用域就是一个模块——存储过程、触发器、函数或批处理。如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。


--delete 
declare @id int ,@name varchar(30)
declare @v_T table(id int,name varchar(30))
DECLARE @Top INT
SET @Top=5
delete top (@Top)  from t output deleted.* into @v_t output deleted.* where name in 
(SELECT TOP (@Top) name
    FROM (SELECT 'sp_msforeachdb' AS name
          UNION ALL
          SELECT 'sp_msforeachtable'
          UNION ALL
          SELECT 'sp_mointor'
          UNION ALL
          SELECT 'sp_spaceused'
          UNION ALL
          SELECT 'sp_updatestats'
          UNION ALL
          SELECT '@@max_connections'
          UNION ALL
          SELECT '@@pack_received'
          UNION ALL
          SELECT '@@pack_sent'
          UNION ALL
          SELECT '@@pack_errors') a
    ORDER BY name)


--update 
declare @name varchar(300)
declare @v_T table(id int,newname varchar(30),oldname varchar(30))
update top (5) t set @name=name=(select max(name) from t)
output inserted.id,inserted.name,deleted.name into @v_t 
output inserted.id,inserted.name,deleted.name  where name in 
(SELECT TOP (5) name
    FROM (SELECT 'sp_msforeachdb' AS name
          UNION ALL
          SELECT 'sp_msforeachtable'
          UNION ALL
          SELECT 'sp_mointor'
          UNION ALL
          SELECT 'sp_spaceused'
          UNION ALL
          SELECT 'sp_updatestats'
          UNION ALL
          SELECT '@@max_connections'
          UNION ALL
          SELECT '@@pack_received'
          UNION ALL
          SELECT '@@pack_sent'
          UNION ALL
          SELECT '@@pack_errors') a
    ORDER BY name)
select * from @v_t
select @name

--清除测试表
drop table t
--以上指是抛砖引玉，举一反三。希望大家能学习到其中的奥秒。