Delete top N rows & Truncate table data - MSSQL

This example shows how to delete top N rows in MSSQL, where N is a dynamic number. It could be 1000, 100, or 10.

  ;WITH CTE AS  
(
SELECT TOP 1000 *
FROM [table_name]
ORDER BY a1
)
DELETE FROM CTE


  • 1000 is an example number.
  • [table_name] is the table you want to delete
  • a1 is just an example of sort by column a1
Thanks to stack overflow for this help :

http://stackoverflow.com/questions/8955897/how-to-delete-the-top-1000-rows-from-a-table-using-sql-server-2008

If you want to delete all table data in the fastest way, you can use the Truncate Keyword. 

The example Table name is: Analytics_Compaign

  TRUNCATE TABLE Analytics_Campaign;  
  /* Reset ID to 1, normally primary key id is auto increment, so when you truncate  
    table data, and when new data insert into database, the id will continue from what it left.  
       This is how you reset the id to number 1  
       DBCC CHECKIDENT (<tablename>,RESEED,<number to set>)*/  
  DBCC CHECKIDENT (Analytics_Campaign, RESEED, 1)  


Popular posts from this blog

Example to disable save as certain file type in SSRS Report Viewer

How to create DataGrid or GridView in JSP - Servlet

Control Webpart Visible/Enable using macro in Kentico