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)