Top in SQL SERVER 2008

We can use the top keyword with the following queries:

1. Select
2. Insert
3. Delete
4. Update

Basically, most of us know about top, we can use it in a select query to get the top element from the query.

But top can also be used in Insert, Delete and Update commands for the table as well.

We will see how we can use top with Insert, Delete and Update.

For this example the following structure is used for the table:

CREATE TABLE #ALL_Student
(
      ID INT IDENTITY(1,1)
      ,SFName varchar(15)
      ,SLName varchar(15)
      ,[Subject] varchar(20)
      ,Marks float
)

And the same structure for #Student.

Terms :- SFName = Student First Name

SLName = Student Last Name

We have data in the #All_Student table and we are picking up the top data from it and inserting it into #Student.

#All_Student table data



1 Top with select statement

Syntax: - SELECT TOP 10 * FROM <TNAME>

2 Top with Insert Statement

It is when we have to insert only the top few records that we can use this:

Syntax:-

Insert top (10) into <TName> (column1, column2.....)
Select column1, column2..... From <TName1>
or
Insert into <TName> (column1, column2.....)
Select TOP (10) column1, column2..... From <TName1>

Example:-

-- 2] TOP WITH INSERT STATEMENTS....
-- I WANTE TO INSERT TOP 2 STUDENT FROM SUBJECT ENGLISH WHO SCORES MORE

INSERT TOP (2) INTO #STUDENT
SELECT SFName,SLName,Subject,Marks
FROM #ALL_Student
WHERE Subject = 'ENGLISH'
ORDER BY Marks DESC

3 Top with Delete Statement

Deleting top records from a table:

Syntax: - Delete top (10) from <TName> where <Cond>

Example:-

--3] TOP WITH DELETE STATEMENT... 
--- I WANT TO DELETE TOP 1 STUDENT FROM #STUDENT TABLE HAVING SUBJECT ENGLISH.
DELETE TOP (1) FROM #STUDENT WHERE Subject ='ENGLISH'

4 Top with Update statement

Updating top records from a table:

Syntax: - Update top (10) <TName> set Colmn1=<value>

Example: -

--4] TOP WITH UPDATE STATEMENT.... 
--- I WANT TO UPDATE TOP 1 STUDENT FROM #ALL_STUDENT TABLE AND CHANGES HIS SUBJECT FROM SANSKRIT TO HINDI
UPDATE TOP (1) #ALL_Student SET Subject='Hindi',Marks = NULL WHERE Subject='SANSKRIT'

NOTE: There is no business logic related to the last query, I just wanted to show how we can use top (1) with an update query. Also there is no such concept in the subject; it is impossible to change Sanskrit to Hindi in real life example.