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 Leave a 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

SQL Server – Add Date/Time to output file of BCP / SQLCMD (2)

April 12, 2013 1 comment

Last time I posted about How you can add date/time to output file name, in which I used xp_cmdshell to execute the BCP/SQLCMD command using TSQL, which means we need to have xp_cmdshell server feature enabled for that solution work. There is a workaround available to that solution when xp_cmdshell is not enabled.

You can also run BCP/SQLCMD command on command prompt (cmd.exe) and append date/time to output file name. You can use below commands:

 

SQLCMD command to add date/time to output filename:

SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt

BCP command to add date/time to output filename:

bcp "EXEC ExportData" queryout MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt -S (local) -T -d SqlAndMe -c

 

You can use ECHO command to verify the file name:

ECHO MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt

 

How this works:

On command prompt %date% return current date in short format. The ":~6,4" part is like a SUBSTRING function which returns 4 characters starting from position 6, which returns year. Similarly, we are retrieving month, day, hour, minutes using same function and appending all of this together to generate the file name in format "MyFile_YYYYMMDD_HHMM.txt"

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 – Add Date/Time to output file of BCP / SQLCMD

April 9, 2013 1 comment

You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify the logic to suit your requirement.

Step 1: First let us create a stored procedure which will provide the data to be exported:

CREATE PROCEDURE ExportData

AS

SET NOCOUNT ON

SELECT 'Vishal', 'SqlAndMe'

GO

 

EXEC dbo.ExportData

GO

Result Set:

—— ——–

Vishal SqlAndMe

I have selected string here to keep things simple. You can specify any query in stored procedure which produces required data.

 

Step 2: Now, we will write the T-SQL code to export data returned from this stored procedure. Here we will use SQLCMD (you can also use BCP) to export data. We will execute SQLCMD using xp_cmdshell extended stored procedure.

DECLARE       @sqlCommand   VARCHAR(1000)

DECLARE       @filePath     VARCHAR(100)

DECLARE       @fileName     VARCHAR(100)

 

SET    @filePath = 'C:\Temp\'

 

SET    @fileName = 'MyFile_' +

       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +

         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +

         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'

 

SET    @sqlCommand =

       'SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o "' +

       @filePath + @fileName +

       '" -h-1'

 

–Uncomment if you want to use BCP

–SET  @sqlCommand =

–     'bcp "EXEC ExportData" queryout "' +

–     @filePath + @fileName +

–     ' " -S (local) -T -d SqlAndMe -c'

 

–PRINT       @sqlCommand

 

EXEC   master..xp_cmdshell @sqlCommand

GO

The above code will create the required file as "MyFile_YYYYMMDD_HH_MM.txt".

You can verify the command generated by uncommenting the PRINT statement in the code above. Also, you can uncomment the fourth SET statement in case you want to use BCP command to export the data.

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 – Saving Changes Not Permitted in Management Studio

April 1, 2013 1 comment

SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:

image

Here, I have tried changing data type for 'Name' column from NVARCHAR(50) to CHAR(50). Since this requires table re-creation Management Studio does not allow this operation to be completed, and it is doing so for your own good!

This activity can be completed without re-creating table if you are using T-SQL. Following T-SQL code can be used to avoid table re-creation:

USE [SqlAndMe]

GO

 

ALTER TABLE [dbo].[Customers]

ALTER COLUMN Name CHAR(50)

GO

The above statement will change the data type of the 'Name' column to CHAR(50) without having to re-create table.

We are better off with this option enabled. Although, if you are a GUI fan you can disable this safety net.

1. Go to Tools > Options

2. Go to Designers > Table and Database Designers

3. Uncheck Prevent saving changes that require table re-creation.

image

You can find more information on why not to disable this option here:

SQL Server – ALTER COLUMN – Management Studio v. T-SQL

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 248 other followers