Remove duplicate rows from table and Copy one table to another table

To Copy One table to another

create table #TT_Employee

(Emp_id int,

Emp_name varchar(20))

insert into #TT_Employee values(1,'Andy')

insert into #TT_Employee values (1,'Andy')

insert into #TT_Employee values(2,'Bill')

insert into #TT_Employee values(2,'Bill')

insert into #TT_Employee values (2,'Bill')

insert into #TT_Employee values (3,'Chris')




-- Method 1 : Select * into table1 from table2
 -- Method 2 : Insert into table2 select * from table1Insert the distinct rows from the duplicate rows table to new temporary table. Delete data from table which has duplicate rows then insert the distinct rows from the temporary table as shown below.
select distinct * into #tmp from #TT_Employee
truncate table #TT_Employee
insert into #TT_Employee select * from #tmp
drop table #tmp

If you want to consider only few columns in a table for duplication criteria to delete rows then Method 1 will not work(in our example, if EMDup table has more than 2 columns and delete rows if empid and name repeats more than one time).

In this case, Add an identity column as a serial number that acts as a row unique identifier(auto incremental ascending order).Then get the Rank against each empid,name. If Rank is greater than 1 means it is a duplicate row and delete the same. After deleting the duplicated rows, remove the identity column which is used for rank. See the below example.

 alter table EmpDup add  sno int identity(1,1)
    delete from EmpDup where sno in
    (
    select sno from (
    select *,
    RANK() OVER ( PARTITION BY empid,name ORDER BY sno DESC )rank
    From EmpDup
    )T
    where rank>1
    )

    alter table EmpDup 
    drop  column sno

Method 3:

Using "Delete Top( )" clause:

If you want to delete duplicate rows for a particular empid then use "Top()" command in delete query as shown below.

    delete top(2) From EmpDup where empid=2

OR

    delete top(select count(*)-1 From EmpDup x where x.empid=2) From EmpDup where empid=2
    

Method 4:

If you want to delete all the rows if the selected columns repeated more than 1 time then use below query.

Query to delete 3 duplicated rows (in our example table) or repeated more than 1 time.

    delete from EmpDup where EmpID in(select EmpID from EmpDup group by EmpId having
    count(*) >1)
    

http://www.c-sharpcorner.com/UploadFile/rohatash/using-case-with-select-and-update-statement-in-sql-server-20/

Update one row value with other value in second row.

create table #my
(EmpId int,
sex varchar(100))
insert into #my (EmpId ,sex)
select 1,'Male'
Union
select 2,'female'
Union
select 3, 'Male'
Union
select 4, 'Male'
Union
select 5, 'female'
select * from #my

update #my SET sex =case 
when sex='Male' then 'Female' 
when sex='female' then 'Male'
end