Jerry Nixon on Windows: Using the TOP keyword in SQL 2005 Insert, Update, and Delete statements.

Jerry Nixon on Windows

Friday, March 14, 2008

Using the TOP keyword in SQL 2005 Insert, Update, and Delete statements.

This simple sample shows how to use the TOP statement which is best know for SQL SELECT statements, in your INSERT, UPDATE, and DELETE statements. It's a cool option.

/*

TOP Insert, Update, Delete

*/

SET NOCOUNT ON

CREATE TABLE [t] (id uniqueidentifier)
Declare @i int; Set @i = 0
While @i < 100
Begin
Insert Into [t] Select NewId()
Set @i = @i + 1
End
Select Count(*) From [t]

SET NOCOUNT OFF

-- delete 10 when 10+ is possible

Delete Top (10) From [t];
Select Count(*) From [t]

-- update 10 when 10+ is possible

Update Top (10) [t]
Set id = null;
Select * From [t]

-- insert 10 when 10+ is possible

Insert Top (10) [t]
Select id From [t] where id is not null;
Select * From [t]

DROP TABLE [t]

GO