Tuesday, April 17, 2012

Merge Command In Sql Server

Merge Command:- Merge command is a new feature introduced in the SQL Server 2008. It can perform Update, Insert and delete operations at a single statement which means all the data is processed and read only once instead of three times(In case of Insertion, deletion and update statements.) It has a target table and Source table. These two tables are join based on a conditions and depending upon whether the Condition is matched or not, Update, Insertion and Deletion Operations are performed.

The Syntax of the Merge command is given below:-

THEN  ];

For example, suppose we have two tables in which Import_Employee is a source table and Employee is a target table.

The query for creating the Source table Import_Employee and inserting its data is  given below:-

Create table Import_Employee (Empno int, firstname nvarchar(100), lastname nvarchar(100))

Insert into Import_Employee (Empno, firstname, lastname)

select 1,'Vivek', 'Johari'

Union All

select 2,'Ravi', 'Kumar'

Union All

select 3,'Uma', 'Sharma'

Union All

select 4,'Neha', 'Sharma'

Select * from  Import_Employee

The query for creating the Target table Employee and inserting its data is given below:-

create table Employee(Empno int, fname nvarchar(100), lname nvarchar(100),enddate datetime)

Insert into Employee (Empno, fname, lname)
select 3,'Jagdish', 'Johari'
Union All
select 4,'Neha', 'Sharma'
Union All
select 5,'Sunny', 'Rana'

Select  *  from Employee

Suppose we have the following requirements:-
1) If a empno is exists in both the table then the corresponding values of the column Fname and Lname for the empno in the table Employee is updated by the corresponding values of the column  firstname and  lastname of the Empno from the source table Import_Employee  .
2) If there is no corresponding empno in the Target table Employee for a empno of the table Import_Employee, then the data from the Source table is inserted into the target table for that empno.
3) If there exists a records for a empno in the Target table whose corresponding records are not in the Source table, these these record should be deleted from the target table.

Therefore, the condition on which the target table and Source table is joined will be the Empno.

The merged statement fulfilling all the above three requirements are given below:-


USING Import_Employee AS SOURCE

ON TARGET.empno=SOURCE.empno


THEN update set TARGET.fname=SOURCE.firstname,

TARGET.lname = SOURCE.lastname






Select * from 

In the above example, the target table "Employee" and the source table "Import_Employee " has the same empno 3, so the fname and the lname of the table employee is updated with the corresponding  firstname and lastname column value from the table Import_Employee  for empno 3.

Also the table employee does not have the record for the empno 1 and 2. Therefore the corresponding record for the empno 1 and 2 will be inserted from Import_Employee (source) table to the Employee(Target) table.

Again, the target table employee contains the records for the empno 5 whereas the source table Import_Employee  do not contains any records for empno 5 , therefore the record for empno 5 is deleted from the target table by this Merge statement.

So we can see from the above example that with the help of Merge statement, we can perform the update, delete and insert commands within a single statement.


Sunday, April 15, 2012

Adding , Deleting and Updating a Column in a table

Many times we need to alter the table definition by adding , deleting or updating a column in the table. In this article, I am trying to explain the following :-

1. How to add a column
2. How to update a column
3. How to drop a column

Suppose we have a table say Employee whose structure is given below:-

CREATE TABLE Employee (Emplyeeid int identity(1,1),
Empnumber nvarchar(10),
Firstname nvarchar(50),
Lastname nvarchar(150),
Age int,
Phoneno nvarchar(15),
Address nvarchar(200),
Empdate datetime)

Insert query for Inserting data in the table is given below:-

Insert Into employee Values
('mani123','Manish', 'Arora',27,'245121','Delhi',getdate())

We can see the data in table employee through the following SQL Statement:-

Select * from Employee

Now suppose we want to update the column Firstname of the table Employee by increasing its character limits from 50 to 100.  The SQL statement used for this column updation will be:-

Alter table Employee Alter column Firstname nvarchar(100)

Again suppose we want to add a column Regdate into the table Employee then the Sql statement for adding the column into the table Employee will be given below:-

Alter table Employee Add Regdate datetime null

The above SQL statement will add a nullable column Regdate to the table which have the datatype as datetime.

The insert command after the addition of the new column regdatum will be given below:-

Insert Into employee Values
('VJO','Vivek', 'Johari',28,'244555121','Delhi',getdate(),'01/01/2012')

Select * from Employee

Suppose if we want to delete a column from the table then we have to write the following SQL Statement

Alter Table Employee Drop Column Regdate

This command will drop the column Regdate from the table Employee which we can verify with the help of the select command.

Select * from Employee

from the above picture, it can be easily see that the column Regdate is not a column of the table Employee anymore.

Derived Table In Sql Server

Derived tables:-  Derived tables are the tables which are created on the fly with the help of the Select statement. It is different from the temporary table in the way that  in case of temporary table, first we have to create a temporary table,  insert the data into the table, select the data from the temporary table and then we have to drop the temporary table. But in case of derived table, SQL Server itself create and populate the table in the memory and we can directly use it. Also we don,t need to drop it. But it can only be referenced  by the outer Select query who created it. Also since it is reside in the memory itself, it is faster then Temporary tables which are created in the temp database.

Suppose, there is a table say tbl_studentcoursereg to store the id of the students registered for various courses, registration month and date. Table structure is given below:-

create table tbl_studentcoursereg (id int identity(1,1),  studentid int, coursename nvarchar(100), Regmonth nvarchar(50), regdate datetime)

The insert command to populate this table with the data is given below:-

insert into tbl_studentcoursereg
values (1, 'C#', 'JAN','01/01/2012')

insert into tbl_studentcoursereg
values (2, 'SQL', 'JAN','01/02/2012')

insert into tbl_studentcoursereg
values (3, 'C++', 'JAN','01/03/2012')

insert into tbl_studentcoursereg
values (4, 'C#', 'FEB','02/02/2012')

insert into tbl_studentcoursereg
values (5, 'C#', 'MAR','03/03/2012')

insert into tbl_studentcoursereg
values (6, 'JAVA', 'APR','04/04/2012')

insert into tbl_studentcoursereg
values (7, 'JAVA', 'APR','04/04/2012')

insert into tbl_studentcoursereg
values (8, 'ORACLE', 'APR','04/04/2012')

insert into tbl_studentcoursereg
values (9, 'C#', 'APR','04/23/2012')

insert into tbl_studentcoursereg
values (10, 'C#', 'MAY','05/05/2012')

Now if we want to know in which month number of students registered for a particular months is greater than 2, the we can use the following query which uses the derived table.

select regmonth, totalstud, totalcourse from 
(select  regmonth ,count(studentid) as totalstud ,
count(coursename)as totalcourse from tbl_studentcoursereg group by regmonth )as tbl1  where tbl1.totalstud>2

In the above query, the following is the derived table:-

(select  regmonth ,count(studentid) as totalstud ,
count(coursename)as totalcourse from tbl_studentcoursereg group by regmonth ) as tbl1

In case of derived table, we have to give the alias name to the derived table as tbl1 is the alias for the above mention derived table.


Saturday, April 14, 2012

Pivot and Unpivot table in SQL SERVER

Pivot Table:- Pivot tables are used to summarize and display the data, specially in case of report data by means of aggregating the values. Pivot table can be used if we want to display the unique values of the column of a table as the columns of another table. It turns the unique values of a specified column into another table columns.

The syntax for the Pivot is given below:-

SELECT non-pivoted column,
    firstpivotedcolumn AS column name,
    secondpivotedcolumn AS column name,
    lastpivotedcolumn AS column name
    (SELECT query that produces the data>)
    AS aliasforsourcequery
    aggregation function(column being aggregated)
column that contains the values that will become column headers
    IN ( firstpivotedcolumn,secondpivotedcolumn,
    last pivoted column)
) AS aliasforthepivottable (optional ORDER BY clause) 

For example, suppose we have a table called tbl_student which contains the columns studentname, grade and marks. The query for creating this table and inserting data is given below:-

Syntax for creating the database:-

Create database DB_Pivot

Query for creating table:-

Create table tbl_student (studentname nvarchar(200), grade nvarchar(10), marks int)

Query for inserting the data into the table:-

Insert into tbl_student (studentname,grade,marks)
Select 'Vivek Johari','I',30
Union All
Select 'Vivek Johari','II',20
Union All
Select 'Vivek Johari','III',35
Union All
Select 'Vivek Johari','IV',40
Union All
Select 'Vivek Johari','V',45
Union All
Select 'Avinash Dubey','I',30
Union All
Select 'Avinash Dubey', 'II', 2
Union All
Select 'Avinash Dubey', 'III' ,35
Union All
Select 'Chandra Singh', 'I', 30
Union All
Select 'Chandra Singh', 'II', 20
Union All
Select 'Chandra Singh', 'III', 35
Union All
Select  ' Pankaj Kumar', 'I', 33
Union All
Select ' Pankaj Kumar', 'II', 29

Now if we want to see the data in the table tbl_student, it will looks like shown below:-

Select  *  from tbl_student

Suppose we want to display the data as shown below:- 

Studentname       I            II               III             IV                  V
Vivek Johari          30        20           35          40              45
Chandra Singh      30        20           35
Avinash Dubey      30        20           35                       
Pankaj Kumar       33         29 

Then we can either use the Select......... Case statement or the Pivot command.
In this article I am going to show the use of the Pivot operator to display data as shown above:-

Select studentname, [I], [II], [III], [IV] , [V]
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc

Or we can use the given below query also:-

Select studentname, [I], [II], [III], [IV] , [V] from tbl_student
Pivot  (  avg(marks) for grade in ([I],[II],[III],[IV],[V])) as pivotable order by V desc,IV desc,III desc,II desc,I desc

Both the query will gives the same result. In the first query we use the Derived table as the Source table and in the 2nd query we use the table tbl_student as the source table.

Unpivot table:- Unpivot table is reverse of Pivot table as it rotate the columns of a table into the value of a column. For example, suppose we have a table say tbl_stdmarksdata whose structure us given below:-

Create table tbl_stdmarksdata  (studentname nvarchar(100), I int, II int, III int, IV int, V int)

Query for inserting data in this table is given below:-

Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V)
Select 'Vivek Johari',30,20,35, 40, 45
Union All
Select 'Chandra Singh',30,20,35,44, 80
Union All
Select 'Avinash Dubey',30,25,35,20, 39
Union All
Select 'Pankaj Kumar',33,29,30, 60, 50

After insert,  the data in the table :-

select * from tbl_stdmarksdata 

The Query for the Unpivot table will be as follow:-

select studentname,Marks,Grade

from tbl_stdmarksdata
(Marks for Grade in (I,II,III,IV,V) ) as tblunpvt

Please  note:- Also as per the MSDN,
When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher.

Futher reading about the Pivot operator can be done at the following Pivot Operator