Friday, October 23, 2009

Delete duplicate records and delete all records from database


WITH CTE (SecondCol,ThirdCol, DuplicateCount)

AS
(
SELECT SecondCol,ThirdCol,
ROW_NUMBER()
OVER(PARTITION BY SecondCol,ThirdCol ORDER BY secondCol)
AS DuplicateCount
FROM testtable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO



How to Delete all the data from the Database with simple Query- Sql Server


use practice
use practice
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO



SELECT ALTERNATIVE ROWS

WITH shital
AS
(
SELECT ROW_NUMBER()OVER(ORDER BY IDA)RN,* FROM EMPLOYEE
)
SELECT *FROM shital WHERE RN%2=0

1 comment:

ambadas said...

Article was very nice to read,keep posting top topics in sql server.