SELECT statements covering all the options.
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ] Identity Insert Syntax for SQL Server
SET IDENTITY_INSERT IdentityTable ON
INSERT IdentityTable(TheIdentity, TheValue) VALUES (3, 'First Row')
SET IDENTITY_INSERT IdentityTable OFF
Deleting duplicate records from table using CTE (Common Table Expression)
WITH [CTE DUPLICATE] AS
(
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY CompanyTitle ORDER BY LastContactDate DESC),
Id, CompanyTitle, ContactName, LastContactDate
FROM Suppliers
)
DELETE FROM [CTE DUPLICATE] WHERE RN > 1
Tech Corner 