SQL | Tuvian : Discussion forum for ASP.Net,C#,Ajax,JQuery,JavaScript,HTML,CSS..

How to implement SQL Bulk Copy in SQL Server OR Bulk insert into SQL Server using SQL BulkCopy

October 17, 2012 infotuvian 1 comment

SqlBulkCopy for insert multiple rows of data into table in SQL Server

Sometimes we need to insert bulk amount of data into a table in SQL. We can insert bulk data to SQL in different methods. Here we are going to demonstrate how to insert large amount of data using SQL Bulk Copy method.

Example of SQL BulkCopy method

Here we are going to insert multiple rows of sample data into Employee table. First of all create a table in SQL named Employee. After that create sample data for employee table and finally this multiple rows of employee data insert into table using SQL bulk copy method.

Create a Table in SQL

CREATE TABLE Employee
(
ID INT,
EmpName varchar(200),
Department varchar(200),
DOB DateTime
);

SQLMulkCopy Example in SQL Server

C# Code to generate sample data and insertion using SQL Bulk Copy

private void loadDataBySQLBulkCopy()
        {
            DataTable dtTblEmployee = new DataTable();
            dtTblEmployee.Columns.Add("ID", typeof(Int32));
            dtTblEmployee.Columns.Add("EmpName", typeof(String));
            dtTblEmployee.Columns.Add("Department", typeof(String));
            dtTblEmployee.Columns.Add("DOB", typeof(DateTime));

            DataRow dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 1; //Bind Data to Columns
            dtrow["EmpName"] = "Wazeem";
            dtrow["Department"] = "Admin";
            dtrow["DOB"] = "1990-10-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 2; //Bind Data to Columns
            dtrow["EmpName"] = "Aslam";
            dtrow["Department"] = "HR";
            dtrow["DOB"] = "1987-05-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 3; //Bind Data to Columns
            dtrow["EmpName"] = "John";
            dtrow["Department"] = "Finance";
            dtrow["DOB"] = "1992-12-11 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 4; //Bind Data to Columns
            dtrow["EmpName"] = "Mishal";
            dtrow["Department"] = "Infra structure";
            dtrow["DOB"] = "1989-04-01 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            SqlBulkCopy bulkCopy = new SqlBulkCopy(
                "server=TEST;database=TEST;uid=test;password=test",
                SqlBulkCopyOptions.TableLock);
            bulkCopy.DestinationTableName = "dbo.Employee";
            bulkCopy.WriteToServer(dtTblEmployee);

        }

Categories: ASP.Net, Csharp, DotNet, SQL Tags: Bulk insert data to SQL, SQLBulkCopy Example, SqlBulkCopy.DestinationTableName, SqlBulkCopy.WriteToServer, SqlBulkCopyOptions.TableLock

Example for All Types of SQL JOIN (Inner Join, Cross Join, Outer Join, Self Join)

September 9, 2012 infotuvian Leave a comment

Definition and very simple examples for each JOINS in SQL

Here we are demonstrating with examples all types of JOINS in SQL Server. First of all we created sample tables and as per the table data we are explaining each SQL JOINS with most understandable examples.

Sample tables

In the following tables, Department.DepartmentID is the primary key, while Employee.DepartmentID is a foreign key.

Employee Table
LastName	DepartmentID
Rafferty	31
Jones	33
Steinberg	33
Robinson	34
Smith	34
Jasper	NULL

Department Table
DepartmentID	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

Inner join

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) – then return all records which satisfy the join predicate.

SQL specifies two different syntactical ways to express joins. The first, called “explicit join notation”, uses the keyword JOIN, whereas the second uses the “implicit join notation”. The implicit join notation lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates. Those filter-predicates function comparably to join-predicates in the explicit notation.

One can further classify inner joins as equi-joins, as natural joins, or as cross-joins (see below).

Example of an explicit inner join:

SELECT *
FROM   employee
       INNER JOIN department
          ON employee.DepartmentID = department.DepartmentID
Is equivalent to:
SELECT * 
FROM   employee, department
WHERE  employee.DepartmentID = department.DepartmentID

Explicit Inner join result:

Employee. LastName	Employee. DepartmentID	Department. DepartmentName	Department. DepartmentID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Steinberg	33	Engineering	33
Rafferty	31	Sales	31

Types of inner joins

Equi-join

An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join.

SELECT Employee.lastName, Employee.DepartmentID, Department.DepartmentName
FROM Employee INNER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
SQL provides optional syntactic sugar for expressing equi-joins,
by way of the USING construct (Feature ID F402):
SELECT Employee.lastName, DepartmentID, Department.DepartmentName
FROM Employee INNER JOIN Department
USING(DepartmentID);

The USING clause is supported by MySQL, Oracle and PostgreSQL.

Natural join

A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

The above sample query for inner joins can be expressed as a natural join in the following way:

SELECT *
FROM   employee NATURAL JOIN department

The result appears slightly different, however, because only one DepartmentID column occurs in the joined table.

DepartmentID	Employee.LastName	Department.DepartmentName
34	Smith	Clerical
33	Jones	Engineering
34	Robinson	Clerical
33	Steinberg	Engineering
31	Rafferty	Sales

Cross join

A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or join-condition is absent in statement.

If A and B are two sets, then the cross join is written as A × B.

The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate.

Example of an explicit cross join:

SELECT *
FROM   employee CROSS JOIN department

Example of an implicit cross join:

SELECT *

FROM employee, department;

Employee. LastName	Employee. DepartmentID	Department. DepartmentName	Department. DepartmentID
Rafferty	31	Sales	31
Jones	33	Sales	31
Steinberg	33	Sales	31
Smith	34	Sales	31
Robinson	34	Sales	31
Jasper	NULL	Sales	31
Rafferty	31	Engineering	33
Jones	33	Engineering	33
Steinberg	33	Engineering	33
Smith	34	Engineering	33
Robinson	34	Engineering	33
Jasper	NULL	Engineering	33
Rafferty	31	Clerical	34
Jones	33	Clerical	34
Steinberg	33	Clerical	34
Smith	34	Clerical	34
Robinson	34	Clerical	34
Jasper	NULL	Clerical	34
Rafferty	31	Marketing	35
Jones	33	Marketing	35
Steinberg	33	Marketing	35
Smith	34	Marketing	35
Robinson	34	Marketing	35
Jasper	NULL	Marketing	35

Outer joins

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Left outer join

The result of a left outer join (or simply left join) for tables A and B always contains all records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

SELECT * 
FROM   employee  LEFT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Employee. LastName	Employee. DepartmentID	Department. DepartmentName	Department. DepartmentID
Jones	33	Engineering	33
Rafferty	31	Sales	31
Robinson	34	Clerical	34
Smith	34	Clerical	34
Jasper	NULL	NULL	NULL
Steinberg	33	Engineering	33

Right outer joins

A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every row from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in A.

A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
SELECT *
FROM   employee RIGHT OUTER JOIN department
          ON employee.DepartmentID = department.DepartmentID

Employee. LastName	Employee. DepartmentID	Department. DepartmentName	Department. DepartmentID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Steinberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality.

Full outer join

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn’t have an employee.

Example full outer join:

SELECT *    
FROM   employee
       FULL OUTER JOIN department
          ON employee.DepartmentID = department.DepartmentID

Employee. LastName	Employee. DepartmentID	Department. DepartmentName	Department. DepartmentID
Smith	34	Clerical	34
Jones	33	Engineering	33
Robinson	34	Clerical	34
Jasper	NULL	NULL	NULL
Steinberg	33	Engineering	33
Rafferty	31	Sales	31
NULL	NULL	Marketing	35

Self-join

A self-join is joining a table to itself.

Example

A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table.

Considering a modified Employee table such as the following:

Employee Table
EmployeeID	LastName	Country	DepartmentID
123	Rafferty	Australia	31
124	Jones	Australia	33
145	Steinberg	Australia	33
201	Robinson	United States	34
305	Smith	United Kingdom	34
306	Jasper	United Kingdom	NULL

An example solution query could be as follows:

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

Which results in the following table being generated.

Employee Table after Self-join by Country
EmployeeID	LastName	EmployeeID	LastName	Country
123	Rafferty	124	Jones	Australia
123	Rafferty	145	Steinberg	Australia
124	Jones	145	Steinberg	Australia
305	Smith	306	Jasper	United Kingdom

Categories: SQL Tags: Cross Join, Cross Join example, Different types of JOINS in SQL, Equi-join, Full outer join, Left Outer Join, Natural join, Outer Joins, Right Outer Join Sample, Self Join, Self Join samples, SQL JOINS with example

How to delete large amount of rows from table in SQL

September 4, 2012 infotuvian 10 comments

How to delete huge amount of data from a table in SQL

In some scenario we have to delete large amount of rows from sql table and it will going to timeout if the table has very large amount of rows (Some tables in the database has more than crore rows). In this scenario we need to delete some small amount of records and from the table and continue the process until all records in the table deleted.

Query for recursive deletion from the table in SQL

Below codes delete 50000 rows recursively untill all records int the table deleted. So it never meets the timeout exception and will complete execution faster than normal query

WHILE exists (
SELECT * FROM myTable WHERE name like ‘%ab%’
 )
DELETE TOP (50000) scanned_cont_dtls WHERE name like ‘%ab%’;

Categories: SQL Tags: Delete all data from a table, Delete large number of rows, delete lots of data from SQL, recursive deletion, SQL, SQL Azure

How to Create and use Table-Valued Parameter in C# and T-SQL/ How to pass table to stored procedures in SQL

July 17, 2012 infotuvian Leave a comment

What is Table – Valued Parameter in SQL Server 2008?

Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

What we are going to do with Table – Valued Parameter?

We are going to demonstrate a very simple example for using Table – Valued parameter. In this sample project we will insert bulk amount of data into the table by passing a bulk data using datatable in C# to SQL stored procedure.

Create a Table for insert data using Table – Valued Parameter

Here we are having a table named Officer and having three fields ID,Name and Salary. We are going to fill the table with bulk data.

CREATE TABLE Officer(
ID INT PRIMARY KEY IDENTITY(1,1),
NAME VARCHAR(50),
SALARY DECIMAL(18, 0))

Stored Procedure for insert data by accepting Table Valued Parameter

Now we are going to create a Stored Procedure that accepting a table type as parameter and insert values in this type into the table.

CREATE PROCEDURE InsertOfficerDetails
(
@OfficerData OfficerDetails readonly
)
AS 
INSERT INTO Officer (Name, Salary)
SELECT Name, Salary
FROM @OfficerData;

C# code to call Stored Procedure to insert data in to the table using Table – Valued Parameter

We are creating a simple ASPX page with a single button. When we click this button we are calling above stored procedure by creating and passing some amount of sample data to the stored procedure as Table – Valued Parameter.

ASPX Page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CallSP.aspx.cs" 
Inherits="ExperimentLab.CallSP" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
 <title></title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <asp:Button ID="btnCallSP" runat="server" Text="Call SP" 
 OnClick="btnCallSP_Click" />
 </div>
 </form>
</body>
</html>

Code Behind

protected void btnCallSP_Click(object sender, EventArgs e)
 {
 try
 {
 DataTable dt = new DataTable();
 DataColumn dtCol = new DataColumn();
 dtCol.ColumnName = "ID";
 dt.Columns.Add(dtCol);
dtCol = new DataColumn();
 dtCol.ColumnName = "Name";
 dt.Columns.Add(dtCol);
dtCol = new DataColumn();
 dtCol.ColumnName = "Salary";
 dt.Columns.Add(dtCol);
for (int i = 0; i < 10; i++)
 {
 DataRow dr = dt.NewRow();
 dr["Name"] = "Name " + i;
 dr["Salary"] = 1000 + i;
 dr["ID"] = i;
 dt.Rows.Add(dr);
 }
 
 string connStr = ConfigurationManager.
 AppSettings["LocalSqlServer"].ToString();
 SqlConnection con = new SqlConnection(connStr);
using (var conn = new SqlConnection(connStr))
 using (var cmd = conn.CreateCommand())
 {
 cmd.Connection = con;
 con.Open();
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.CommandText = "dbo.InsertOfficerDetails";
 SqlParameter param = cmd.Parameters.AddWithValue("@OfficerData", dt);
 cmd.ExecuteNonQuery();
 }
 }
 catch (Exception a)
 {
 Response.Write(a.Message);
 }
 }

Web.Config

<appSettings>
 <add key="LocalSqlServer" 
value="Database=testDB;Server=Servername\SQLEXPRESS;User Id=userid;Password=password"/>
</appSettings>

Hence we discussed about how to create and use Table valued parameters, how to create a store procedure with table type as parameter, how to pass table to stored procedure in C#/Asp.Net, how to insert multiple rows of data to a table with table valued parameter in SQL, how to insert bulk data to SQL table using Table Valued Parameter in SQL Server 2008 etc..

Categories: ASP.Net, Csharp, DotNet, SQL Tags: bulk insert into table using table valued parameter, pass table to stored procedure, table valued parameter

How to UNPIVOT table in SQL Server / UNPIVOT table example in SQL Server

July 12, 2012 infotuvian 10 comments

What is PIVOT and UNPIVOT operator in SQL Server

We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Please go through our previous post for PIVOT sample in SQL

Before UNPIVOT

Before UNPIVOT table

After UNPIVOT

UNPIVOT sample in SQL Server

A simple UNPIVOT sample in SQL Server

Here we are going to demonstrate a very simple UNPIVOT sample without any complexity. We are having a table named EmploymentHistory and its containing employee name with previous company name that particular employee worked.

Table Structure For showing simple UNPIVOT sample

CREATE TABLE EmploymentHistory 
(Id INT, 
Employee VARCHAR(500), 
Company1 VARCHAR(500), 
Company2 VARCHAR(500), 
Company3 VARCHAR(500)
) 
GO

-- Load Sample data
INSERT INTO EmploymentHistory SELECT 
1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki'
UNION ALL SELECT 
2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo'
UNION ALL SELECT 
3, 'Sam', 'Hercules', 'Hero', 'Atlas'
GO

In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee with each company as a single row.

SELECT Id, 
ROW_NUMBER()OVER(Order By ID) as NewID,
Employee, 
Company
FROM 
( 
SELECT Id, Employee, Company1, Company2, Company3
FROM EmploymentHistory 
) Main
UNPIVOT
( 
Company FOR Companies IN (Company1, Company2, Company3) 
) Sub

UNPIVOT Table with multiple columns

Now we are going to UNPIVOT table with multiple columns. Suppose we are having a table with Employee name, list of company names that particular employee worked and corresponding cities also. The table structure like below.

CREATE TABLE EmploymentHistoryWithCity 
(Id INT, 
Employee VARCHAR(500), 
Company1 VARCHAR(500), 
Company2 VARCHAR(500), 
Company3 VARCHAR(500),
City1 VARCHAR(500), 
City2 VARCHAR(500), 
City3 VARCHAR(500) 
) 
GO
-- Load Sample data 
INSERT INTO EmploymentHistoryWithCity SELECT
1, 'John', 'Tata Motors', 'Ashok Leyland', 'Suzuki', 'Mumbai', 'Kolkata', 'Delhi'
UNION ALL SELECT 
2, 'Kate', 'Airtel', 'Vodafone', 'Tata Docomo', 'Chennai', 'Kolkata', 'Banglore'
UNION ALL SELECT 
3, 'Sam', 'Hercules', 'Hero', 'Atlas', 'Delhi', 'Mumbai', 'Banglore'
GO

Before UNPIVOT multiple columns table

 

In order to UNPIVOT above table we can use below mentioned script. The result should be as Employee name with Company1 and City1 as a first row, Employee Name with Company2 and City2 as second row and so on
SELECT Id, 
ROW_NUMBER()OVER(Order By ID) as NewID,
Employee, 
Company, 
City 
FROM 
( 
SELECT Id, Employee, Company1, Company2, Company3, City1, City2, City3
FROM EmploymentHistoryWithCity 
) Main
UNPIVOT 
( 
Company FOR companies IN (Company1, Company2, Company3) 
) Sup
UNPIVOT 
( 
City For Cities IN (City1, City2, City3 ) 
) Ct
WHERE RIGHT(companies,1) = RIGHT(Cities,1)

After UNPIVOT multiple columns table

Categories: SQL Tags: How to UNPIVOT table in SQL with multiple columns, PIVOT and UNPIVOT exampe in SQL, PIVOT and UNPIVOT in SQL defenition, Simple UNPIVOT table, UNPIVOT multiple columns, UNPIVOT sample in SQL Server

How to PIVOT table in SQL Server / PIVOT table example in SQL Server

July 10, 2012 infotuvian 2 comments

What is PIVOT and UNPIVOT operator in SQL Server

Please go through our post for simple UNPIVOT example

Before PIVOT

Before PIVOT table

After PIVOT

After PIVOT table

A simple PIVOT sample in SQL Server

Here we are going to demonstrate a very simple PIVOT sample without any complexity. We are having a table named Accounts and it containing customer name and their deposit with denominations.

Table Structure For showing simple PIVOT sample

CREATE TABLE Accounts(Customer VARCHAR(25), Denomination VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','10 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','50 $',6)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','100 $',1)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','10 $',4)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','50 $',3)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','100 $',11)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','10 $',20)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','50 $',12)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','100 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','10 $',0)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','50 $',5)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','100 $',5)

In order to PIVOT above mentioned table we can use below script. The result should be as Customer name with all denomination will be coming as columns with qty as values for each column.

SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN ([10 $],[50 $],[100 $])) AS Total

Dynamic Query to PIVOT table for dynamic columns

In the above example we are using a simple structured table and then PIVOT with denomination values. This can be achieved only when we are having denomination values as static. Suppose this denomination values are dynamic (Each country having different denomination like $,EUR, IND etc..), we need to create a dynamic query to PIVOT above table.

Suppose we are having different table for getting Denomination values and we are going to take Denomination values from this table at run time as dynamic.

CREATE TABLE Denomination(Value VARCHAR(25))
GO
INSERT INTO Denomination(Value)
VALUES('10 $')
INSERT INTO Denomination(Value)
VALUES('50 $')
INSERT INTO Denomination(Value)
VALUES('100 $')

First of all, we need to get dynamic columns names from the above table. After that we can create a dynamic query with these columns.

Declare @ColumnNames VARCHAR(100);
SELECT @ColumnNames = COALESCE(@ColumnNames+ ',','') +
'['+ Cast(Value AS VARCHAR(50)) +']' FROM Denomination cust
PRINT @ColumnNames
DECLARE @DynamicQuery Varchar(MAX);
SET @DynamicQuery = '
SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN (' + @ColumnNames + ')) AS Total'
EXEC (@DynamicQuery);

Categories: SQL Tags: Dynamic columns for PIVOT query in SQL, Dynamic Query for PIVOT Table Sample, How to PIVOT table in SQL, PIVOT and UNPIVOT in SQL Server 2008

How to Calculate Age in Sql Server OR Find age using SQL query

July 3, 2012 infotuvian 2 comments

Calculate age of employee using SQL Server

To calculate an age from the SQL table is little bit tricky. We are demonstrating how to calculate the age from the date of birth fields in the SQL table. While fill the application form it’s better to fill the Date Of Birth field instead of age field. Because age field can calculate from the Date Of Birth field easily and accurately.

Find the age from the Date Of Birth using SQL Server

Here we are having a table called Employee and having coloumns ID,Name and DOB. From this table we need to find the age of each employee using SQL script.

Please see the table structure.

Calculate age from SQL Server

We can use Date Diff function in SQL Server to get the age of each employee. But if we consider date field only it will get wrong answer because it will consider the current year even date of birth not came this year. The below script and result is example.

Calculate age from SQL Server

For avoiding above mentioned issue, we can use below script to find out the age from the Date Of Birth field using SQL Server.

SELECT DOB AS DateOfBirth,
GETDATE() AS CurrentDate,
DATEDIFF(YEAR,DOB,GETDATE()) - 
(CASE WHEN 
 DATEADD(YY,DATEDIFF(YEAR,DOB,GETDATE()),DOB) > GETDATE()
 THEN 1
 ELSE 0 END) AS Age
FROM Employee

Calculate age using SQL Server

Categories: SQL Tags: Age calculating script, Calculate Age using SQL Server, Find Age in SQL Server

SQL Query for truncate all tables in a database in SQL/SQL Azure

September 26, 2011 infotuvian 2 comments

How to delete or truncate all the data from all the tables in a database

For clean up the database or switch to a new database, we may need to truncate or delete all tables in the database. Its not easy to select and delete all tables in a database as the database may having n number of tables. We can get all tables in a database from sys objects and we can apply delete or truncate script to each and every table from a single cursor.

SQL script for delete/truncate all tables in a database in SQL/SQL Azure

The following script truncates all tables in the selected database. If you want to delete all tables in the database we can edit this query with delete statement instead of truncate statement.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('U')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN
     Set @StringVal = 'truncate table ' + @DBObjName
       exec (@StringVal)           
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName

How to drop all stored procedures (sps) and functions from a database

The following script drop all procedures and user defined functions from a database.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('P','FN','TF')
AND Name not in ('DBM_EnableDisableAllTableConstraints')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN

      IF @Type='P'
      BEGIN
            Set @StringVal = 'Drop Procedure ' + @DBObjName
            exec (@StringVal)
      END
      ELSE IF @Type='FN' OR @Type='TF'
      BEGIN
            Set @StringVal = 'Drop Function ' + @DBObjName
            exec (@StringVal)
      END

FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName

Categories: SQL, Windows Azure Tags: Delete all data from a table, Drop all procedure and functions, Drop all tables in SQL Azure

How to find the size and cost of sql azure database

September 15, 2011 infotuvian Leave a comment

SQL query to find the cost and size of sql azure database

SQL Azure database is a paid service as per the usage of the database. So we should have an idea regarding our usage of data in the sql azure database. Then only we can use it as cost effective product. So we need to regularly checking the size and cost of our sql azure database.

Query to find the size and cost of SQL azure entire database

The following query will return the size and cost of the sql azure database. The latest price of the azure storage can be seen here. http://www.microsoft.com/windowsazure/pricing/

The following query will give the size and price of the database as per the following price:

DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

SELECT    (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes

Query to find size and cost of SQL Azure datbase as per indexes

The following query will return the size and cost of sql azure dtabase’s indexes.

 DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

DECLARE @CostPerByte float

SELECT    @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes SELECT idx.name,
SUM(reserved_page_count) * 8192 'bytes',
      (SUM(reserved_page_count) * 8192) * @CostPerByte 'cost'
FROM sys.dm_db_partition_stats AS ps
    INNER JOIN sys.indexes AS idx ON
idx.object_id = ps.object_id AND idx.index_id = ps.index_id
WHERE type_desc = 'NONCLUSTERED'
GROUP BY idx.name
ORDER BY 3 DESC

Query to find out the size and cost of each tables in SQL Azure database

 The following query return the cost per month per row for every table in the database.
DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats
DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )
DECLARE @CostPerByte float
SELECT    @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes
SELECT    
      sys.objects.name,
      sum(reserved_page_count) * 8192 'Bytes',
      row_count 'Row Count', 
      (CASE row_count WHEN 0 THEN 0 ELSE
       (sum(reserved_page_count) * 8192)/ row_count END)
        'Bytes Per Row',
      (CASE row_count WHEN 0 THEN 0 ELSE
       ((sum(reserved_page_count) * 8192)/ row_count)
        * @CostPerByte END)
        'Monthly Cost Per Row'
FROM    
      sys.dm_db_partition_stats, sys.objects
WHERE    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name, row_count

Categories: SQL, Windows Azure Tags: Cost and size of azure database, SQL Azure Pricing

How to implement leading zero to the variable in the SQL Server/Azure

September 7, 2011 infotuvian 1 comment

Leading zero to the variable in SQL Server

To implement leading zero to the variable in SQL Server 2008, we can use following script. In some scenario we have to leading zero to the selected values from SQL server table as per the number of digits in the selected value.

Create a table in SQL Server

CREATE TABLE Employee(Code INT);

We can insert values in to the age column of the Employee table
    INSERT Employee VALUES(’13’);
    INSERT Employee VALUES(‘111′);
    INSERT Employee VALUES(’12’);
    INSERT Employee VALUES(‘322’);
    INSERT Employee VALUES(‘422’);

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

Select Code from Employee table

SELECT * FROM Employee;

Code
13
111
12
322
422

5 row(s) affected.

How to Format column value foor leading zero in SQL Server

Now we are going to implement leading zero to each code up to the 4 digits.

 SELECT RIGHT('0000'+ CONVERT(VARCHAR,Code),4) AS EmployeeCode FROM Employee;

EmployeeCode
0013
0111
0012
0322
0422

If we want to format with 6 digits can use following scripts

SELECT RIGHT('000000'+ CONVERT(VARCHAR,Code),6) AS EmployeeCode FROM Employee;

Categories: SQL Tags: add leading zeros in sql, How to implement leading sero in SQL Server

Older Entries

	关于asp.net：如何从网页将大文件上… on How to upload large size file/…
	Godaddy Outlook 2007… on How to setup Godaddy domain fo…
	Vijay on How to create breadcrumbs in A…
	video game on How to setup Godaddy domain fo…
	video game on How to setup Godaddy domain fo…

Tuvian : Discussion forum for ASP.Net,C#,Ajax,JQuery,JavaScript,HTML,CSS..

Archive

How to implement SQL Bulk Copy in SQL Server OR Bulk insert into SQL Server using SQL BulkCopy

Example for All Types of SQL JOIN (Inner Join, Cross Join, Outer Join, Self Join)

How to delete large amount of rows from table in SQL

How to Create and use Table-Valued Parameter in C# and T-SQL/ How to pass table to stored procedures in SQL

How to UNPIVOT table in SQL Server / UNPIVOT table example in SQL Server

How to PIVOT table in SQL Server / PIVOT table example in SQL Server

How to Calculate Age in Sql Server OR Find age using SQL query

SQL Query for truncate all tables in a database in SQL/SQL Azure

How to delete or truncate all the data from all the tables in a database

How to find the size and cost of sql azure database

How to implement leading zero to the variable in the SQL Server/Azure

Search other topic from here

You Like Blog ?

Email Subscription

Blog Stats

Top Posts

Recent Posts

Archives

Categories

Site Viewers

Recent Comments

Archive

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

How to delete or truncate all the data from all the tables in a database

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Search other topic from here

Email Subscription

Blog Stats

Top Posts

Recent Posts

Archives

Categories

Site Viewers

Recent Comments