/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
IF EXISTS(SELECT * FROM tempdb.Information_Schema.Tables WHERE TABLE_NAME LIKE '#Temp%') ����DROP TABLE #temp CREATE TABLE #temp ([Id] INT, [Name] VARCHAR(50), [Age] INT, [Sex] bit DEFAULT 1) GO INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(1,'James',25,DEFAULT) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(2,'Lisa',24,0) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(2,'Lisa',24,0) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(2,'Lisa',24,0) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(3,'Mirsa',23,0) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(3,'Mirsa',23,0) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(3,'Mirsa',23,0) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(4,'John',26,DEFAULT) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(5,'Abraham',28,DEFAULT) INSERT INTO #temp ([Id] , [Name] , [Age] , [Sex] ) VALUES(6,'Lincoln',30,DEFAULT) DELETE T FROM (SELECT ROW_NUMBER() OVER(Partition BY [ID],[Name],[Age],[Sex] ORDER BY [ID]) AS RowNumber,* FROM #Temp)T WHERE T.RowNumber > 1 SELECT * FROM #temp
URL: http://www.sqlservercentral.com/scripts/duplicate+rows/71078/