Archive

Archive for the ‘Working With Data’ Category

SQL Server – Calculating elapsed time from DATETIME

December 23, 2013 8 comments

Elapsed time can be calculated from DATETIME field by extracting number of hours/minutes and seconds. You can use below query to calculate elapsed time between two dates:

-- Vishal - http://SqlAndMe.com

DECLARE @startTime DATETIME
DECLARE @endTime DATETIME

SET @startTime = '2013-11-05 12:20:35'
SET @endTime = '2013-11-10 01:22:30'

SELECT	[DD:HH:MM:SS] =
	CAST((DATEDIFF(HOUR, @startTime, @endTime) / 24) AS VARCHAR)
	+ ':' +
	CAST((DATEDIFF(HOUR, @startTime, @endTime) % 24) AS VARCHAR)
	+ ':' + 
	CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime)
	THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR)
	ELSE
	CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60)
		AS VARCHAR)
	END
	+ ':' + CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR),
	[StringFormat] =
	CAST((DATEDIFF(HOUR , @startTime, @endTime) / 24) AS VARCHAR) +
	' Days ' +
	CAST((DATEDIFF(HOUR , @startTime, @endTime) % 24) AS VARCHAR) +
	' Hours ' +
	CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime)
	THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR)
	ELSE
	CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60)
	AS VARCHAR)
	END +
	' Minutes ' +
	CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR) +
	' Seconds '

Result Set:

DD:HH:MM:SS    StringFormat
4:13:2:55      4 Days 13 Hours 2 Minutes 55 Seconds

(1 row(s) affected)

 
[UPDATE] Earlier query had an error in calculation, thanks to Carlos for pointing it out and Nate for providing the correct solution.
 
Hope This Helps!

Vishal

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Custom sorting in ORDER BY clause

November 18, 2013 1 comment

ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order.

Sometimes, we need result set to be sorted in a custom order, for example, a specific value must appear at top of result set, and others can be sorted in standard order.

for example, consider following list of countries:

CountryName

AUSTRALIA

BANGLADESH

CHINA

FRANCE

INDIA

JAPAN

NEW ZEALAND

PAKISTAN

SRI LANKA

UNITED KINGDOM

UNITED STATES

Now based on the popularity you might need a country to appear on top of the list. In order to return results as required, we need to specify a custom sort order in ORDER BY clause. It can be used as below.

The following query will return result set ordered by CountryName, but INDIA at top and CHINA at 2nd position:


USE [SqlAndMe]
GO

SELECT CountryName
FROM   dbo.Country
ORDER BY CASE WHEN CountryName = 'INDIA' THEN '1'
              WHEN CountryName = 'CHINA' THEN '2'
              ELSE CountryName END ASC
GO

Result Set:

CountryName

INDIA

CHINA

AUSTRALIA

BANGLADESH

FRANCE

JAPAN

NEW ZEALAND

PAKISTAN

SRI LANKA

UNITED KINGDOM
UNITED STATES

As you can see from the results above, both results are now in desired position, while remaining values are sorted in a standard order.

Another variation we can use to place only one row at top of result set is set it’s order to NULL, since NULLs appear first in ordered result set.


USE [SqlAndMe]
GO

SELECT CountryName
FROM   dbo.Country
ORDER BY CASE WHEN CountryName = 'INDIA' THEN NULL
              ELSE CountryName END ASC
GO

Result Set:

CountryName

INDIA

AUSTRALIA

BANGLADESH

CHINA

FRANCE

JAPAN

NEW ZEALAND

PAKISTAN

SRI LANKA

UNITED KINGDOM
UNITED STATES

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Import text file using xp_cmdshell

July 29, 2013 Leave a comment

There are several options available to import data from external sources to SQL Server. Such as Import & Export Wizard, BULK INSERT command, SSIS and OPENROWSET.

Apart from this options you can also use xp_cmdshell to import text file to SQL Server. We need to utilize dos command TYPE for this purpose. Below script can be used to import a text file to database.


-- Script to import text file using xp_cmdshell

-- Create Temporary table to store data

CREATE TABLE #TempOutput
(
	Result	VARCHAR(MAX)
)

DECLARE	@sqlCommand VARCHAR(1000)
DECLARE	@rCode INT

-- read from text file
SET @sqlCommand = 'TYPE C:\Vishal.txt'

INSERT INTO #TempOutput
EXEC @rCode = master.dbo.xp_cmdshell @sqlCommand

-- display results
SELECT	*
FROM	#TempOutput
GO

-- drop temporary table
DROP TABLE #TempOutput

-- Script End

Above script requires xp_cmdshell to be enabled on server.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – SELECTing/Displaying Top N rows from a table

July 15, 2013 Leave a comment

To SELECT only top N rows from a table we can use TOP clause in SELECT statement. Using TOP clause we can also specify percentage option.

For example, both of these statements are valid:

USE SqlAndMe

GO

 

SELECT TOP 5 EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

 

SELECT TOP 5 PERCENT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

 

(5 row(s) affected)

 

 

 

EmployeeId    FirstName     LastName

1             Ken           Sánchez

 

(1 row(s) affected)

Another way to limit rows in result set is to use SET ROWCOUNT N statement. SET ROWCOUNT N stops processing the query after specified number of rows are returned.

It can be used as below:

USE SqlAndMe

GO

 

SET ROWCOUNT 5

GO

 

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

 

SET ROWCOUNT 1

GO

 

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

 

(5 row(s) affected)

 

 

EmployeeId    FirstName     LastName

1             Ken           Sánchez

 

(1 row(s) affected)

Using SET ROWCOUNT N affects current session, to turn off this setting we need to set it to 0.

SET ROWCOUNT 0

GO

There are some differences between using SET ROWCOUNT N and TOP N [PERCENT]. You can read about these differences here: SET ROWCOUNT VS TOP in SQL Server

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Update Table with INNER JOIN

June 18, 2013 Leave a comment

Often we may need to update a column in a table based of another column in another table.

In SQL Server you can do this using UPDATE statement by joining tables together.

To understand this better let’s take a look at below contrived example.

USE [SqlAndMe]

GO

 

SELECT CustomerID, Name, OrderAmount

FROM   dbo.Customers

GO

 

SELECT OrderID, CustomerID, Amount

FROM   dbo.Orders

GO

Result Set:

CustomerID    Name          OrderAmount

3             Curtis        NULL

4             Lanna         NULL

5             Marlin        NULL

6             Henry         NULL

 

(4 row(s) affected)

 

OrderID       CustomerID    Amount

107           6             8745.00

123           5             4582.00

643           3             5693.00

 

(3 row(s) affected)

In the above data I want to update OrderAmount column of dbo.Customers with values from Amount column of dbo.Orders.

To achieve this we can use UPDATE statement as below:

UPDATE CUST

SET    CUST.OrderAmount = ORDR.Amount

FROM   dbo.Customers CUST

INNER JOIN dbo.Orders ORDR ON CUST.CustomerID = ORDR.CustomerID

GO

 

(3 row(s) affected)

OrderAmount column in dbo.Customers Table is now updated based on JOIN condition.

SELECT CustomerID, Name, OrderAmount

FROM   dbo.Customers

GO

CustomerID    Name          OrderAmount

3             Curtis        5693.00

4             Lanna         NULL

5             Marlin        4582.00

6             Henry         8745.00

 

(4 row(s) affected)

 

I have used a simple example here to explain the concept easily

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – How to Move Table to Another Schema

May 13, 2013 1 comment

Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.

For example, if I create a table using below script it will be created under my default schema which is dbo:

USE [SqlAndMe]

GO

 

CREATE TABLE Employee

(

       ID     INT,

       Name VARCHAR(20)

)

GO

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      dbo

 

(1 row(s) affected)

As you can see from the output the table is currently in dbo schema.

Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.

USE [SqlAndMe]

GO

 

CREATE SCHEMA HumanResources

GO

 

ALTER SCHEMA HumanResources

TRANSFER dbo.Employee

GO

 

SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'

GO

Result Set:

name          schema

Employee      HumanResources

 

(1 row(s) affected)

As you can see from the output the Employee table is now moved to HumanResources schema.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Import Data from Excel using T-SQL

April 15, 2013 4 comments

To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You can also import Excel data using T-SQL OPENROWSET function. OPENROWSET function can be used to import data from and OLEDB data source.

For this example I have created a simple excel file which has a single row of data and column headers, and the sheet is named [Employees] as below:

image

I have stored this file in my C:\ root folder.

 

Now to import this data to SQL Server you can use OPENROWSET as below:

SELECT *

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',

       'SELECT * FROM [Employees$]')

Result Set:

image

As you can see this is very easy and straight forward. However, the above query only displays data from Excel file, it does not import it to database.

To import this data to SQL Server Table you can modify the SELECT statement to SELECT INTO

USE [SqlAndMe]

GO

 

SELECT * INTO dbo.ImportedEmployeeData

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',

       'SELECT * FROM [Employees$]')

GO

The above statement will create a new table ImportedEmployeeData and import excel data to the table.

 

Although this method seems straight forward, there are a few errors you may encounter:

 

Error Message 1:

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error.

The provider did not give any information about the error.

 

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Cause 1: You may receive this error if the Excel file is currently opened.

Solution 1: Close the Excel file if it is currently opened and try executing again.

 

Error Message 2:

Msg 15281, Level 16, State 1, Line 2

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.

 

A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Cause 2: The cause of this error is mentioned in the error message. You may receive this error message if Ad Hoc Distributed Queries are not enabled by system administrator.

Solution 2: You can use sp_configure to enable this component. Since this is an advanced option you also need to enable 'Show Advanced Options' to enable this component. Use below commands to enable this:

EXEC sp_configure 'Show Advanced Options', 1

RECONFIGURE

GO

 

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

GO

Result Set:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

 

Error Message 3:

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Cause 3: You may receive this error message if registry settings are not set properly

Solution 3: To resolve this error, run below commands to fix registry issues:

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

 

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Follow

Get every new post delivered to your Inbox.

Join 277 other followers