Dot net Question and Answers: June 2015

Thursday, June 18, 2015

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

    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

Wednesday, June 17, 2015

Difference between Temporary table and table variable

Table variables are alternative of temporary tables. As,for instance, temporary tables are table variables store a set of record. The syntax of table variable shown below:

We can insert values using INSERT INTO clause in table variable. We can use SELECT to fetch data and UPDATE, DELETE for manipulate data in table variable. In table variable the constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY constraints are not allowed. Table variable Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. We can return a table variable from user-defined function.

It is just like temporary table (#EmpForm) but some differences are there.

A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable we create with a DECLARE statement. Temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed.
Table variable data will not be rolled back when a transaction rolled back. Temporary table data will be rolled back when a transaction rolled back.
The variable will no longer exist after the procedure exits, there will be no table to clean up with a DROP statement. Temporary tables are automatically dropped when they go out of scope unless explicitly dropped using DROP TABLE.
The restricted scope of a table variable gives SQL Server some liberty to perform optimizations.
A table variable will generally use fewer resources than a temporary table.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure.
We cannot use a table variable as an input or an output parameter.
We cannot truncate a table variables whereas we can truncate a temporary table.
We can not drop table variable as it automatically drop as it goes out of scope. We can not run separate command first create and insert and update it should execute in single shot. Whereas Temporary table we can do separate we can see it in temp db also.