You are here:Home » tsql » TOP clause with Insert, Update and Delete statements in SQL Server 2005

TOP clause with Insert, Update and Delete statements in SQL Server 2005



I have mostly used TOP clause to select the records from table but not with Insert, update and delete statement. Here is demonstration of TOP clause with insert/update/delete.

CREATE DATABASE TrasactionDB
GO

USE TrasactionDB
GO

CREATE TABLE topTable
(
Id int identity(1,1)
,Value varchar(10)
)

INSERT INTO topTable
SELECT ('Insert')
UNION ALL
SELECT ('update')
UNION ALL
SELECT ('delete')
GO 

Let’s run the script for the inserting, updating and deleting records from table.

/*  Inserting records with TOP clause */

SELECT TOP(1) Id, Value  INTO copyTable FROM topTable
GO

-- Viewing records in copyTable table 

SELECT Id, Value FROM copyTable
GO
Id Value
1 insert
/* Updating Records with TOP clause*/

UPDATE TOP(2) topTable
SET value = 'updating'

-- Viewing records in topTable

SELECT Id,value FROM topTable
GO
Id value
1 updating
2 updating
3 delete
/* Deleting records with TOP clause */

DELETE TOP(2) FROM topTable

-- viewing records FROM topTable

SELECT Id, value FROM topTable
GO
id value
3 delete