Archive

Archive for the ‘Working With Data’ Category

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

April 12, 2013 2 comments

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

SQL Server – How to Rename Table/Column in SQL Server

March 4, 2013 1 comment

To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.

Method 1: To rename an object using SQL Server Management Studio:

Step 1. Right Click on the object in Object Explorer and select "Rename"

image

Step 2. Specify new name and press Enter.

image

You can rename any object using object in Object Explorer.

 

Method 2: Renaming an object using sp_rename:

You can also rename using sp_rename system stored procedure.

sp_rename takes below arguments:

Parameter Description
@objname Object Name. When renaming a column you need to specify table name.column name optionally you can also prefix schema name
@newname New name for the specified object
@objtype Type of the object. You can rename below objects using sp_rename:
COLUMN
DATABASE
INDEX
OBJECT
STATISTICS
USERDATATYPE
Default value for this parameter is TABLE

Following example demonstrates how you can use sp_rename to rename table and column names:

Let’s create a Test Table to work with:

USE [SqlAndMe]

GO

 

CREATE TABLE tblProduct

(     

       ID     INT,

       pName  NVARCHAR(50)

)

GO

Now we can rename the Table Name and column names as below:

— Rename table tblProduct to Table_Products

EXEC sp_rename 'tblProduct', 'Table_Products'

GO

 

— Rename Column ID to ProductID

EXEC sp_rename 'Table_Products.ID', 'ProductID', 'COLUMN'

GO

 

— Rename Column pName to ProductName

EXEC sp_rename 'Table_Products.pName', 'ProductName', 'COLUMN'

GO

Result Set:

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

Caution: Changing any part of an object name could break scripts and stored procedures.

You can verify that the changes have been made by issuing a SELECT against the table using new table name. SQL Server is also nice enough to warn us that renaming an object can break scripts and stored procedures. What this means is if you have used table/column names in any script/stored procedure it will not be updated automatically, you will need to go through your scripts/stored procedures manually and update them accordingly.

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 – DDL vs. DML Triggers

May 28, 2012 1 comment

Although, DML and DDL triggers are used for different purposes, there are some similarities and differences between these.

 

Similarities between DDL and DML triggers:

1. Both are created using similar T-SQL syntax

2. Both can run .NET code

3. You can create multiple DDL and/or DML triggers on an object

4. The trigger and the triggering statement run as part of the same transaction

5. Both type of triggers can be nested

 

Differences between DDL and DML triggers:

1. There are no INSTEAD OF DDL triggers, they are only executed after the triggering statement is completed.

2. DML triggers use virtual tables INSERTED and DELETED which captures data modification, DDL triggers do not have this

3. DDL triggers captures information about the event that fired them using EVENTDATA() function.

 

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

Importing data from Excel – using INSERT statements

March 29, 2012 6 comments

You can easily import data from an Excel file to SQL Server using SQL Server Import and Export Wizard. However, when the data is simple and limited I avoid using it (too lazy to click through 6 screens… 🙂 ) instead I generate INSERT statements using CONCATENATE function in Excel and execute those. This is much faster than using Import/Export Wizard.

This approach also requires the destination table to be created manually or it should already exists. Here’s how I use it:

1. The destination table I am using already exists with below columns, and some data:

image

2. The source Excel file contains below data:

image

3. Now to convert this to INSERT statement, we need to add a few columns to Excel sheet which will contain these texts "INSERT INTO dbo.ProductList VALUES (' ", " '' "," ' " etc. as shown below:

image

Note: if single quote (') is the first character in the column, as in Column C; you will need to input two single quotes (''). Also, if table has additional columns which are not being imported then you will need to include column list with INSERT statement i.e. "INSERT INTO dbo.ProductList (Name) VALUES (' " if you are importing only Name column.

4. Then you need to concatenate these columns using Excel's CONCATENATE function:

image

5. Finally, copy the same formula to all rows, these are the INSERT statements you need!:

image

6. That's all folks, Now copy them to SSMS and execute them…

 

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

Exporting data to CSV file using SQLCMD/BCP

March 27, 2012 3 comments

When it comes to exporting data there are different options available in SQL Server, such as SQL Server Integration Services, Generating Insert Scripts, BCP, Import and Export Wizard and SQLCMD.

When it comes to exporting data as CSV file, BCP and SQLCMD are the easiest methods. Both are command-line tools which can be used to export data.

General syntax for SQLCMD is as follows:

SQLCMD S <<ServerName>> E Q "EXEC AdventureWorks2008R2.dbo.uspExport"
s "," o "C:\DataSqlCmd.csv" h1

Where, -S specifies Database Server Name,
-E specifies to use Windows Authentication,
-Q specifies the query to be executed against server,
-s specifies column separator character,
-o specifies the destination file and,
-h-1 specifies that column headers should not be exported.

You can provide a SELECT statement or a Stored Procedure to -Q switch. In general I use stored procedures as it allows to do any formatting required which cannot be done using SQLCMD.

I have created the stored procedure dbo.uspExport as below:

USE AdventureWorks2008R2

 

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 BusinessEntityID,

            JobTitle,

            Gender,

            BirthDate

FROM        HumanResources.Employee

The stored procedure simply returns top5 rows which are to be exported.

Executing the above SQLCMD command will generate the CSV file as follows:

          1,Chief Executive Officer                ,M,      1963-03-02
          2,Vice President of Engineering          ,F,      1965-09-01
          3,Engineering Manager                    ,M,      1968-12-13
          4,Senior Tool Designer                   ,M,      1969-01-23
          5,Design Engineer                        ,F,      1946-10-29

Same data can be exported using BCP as follows:

bcp "EXEC AdventureWorks2008R2.dbo.uspExport" queryout "C:\DataBCP.csv" c t , S (local) T

Here, -c specifies the character type format,
-t specifies the column separator,
-S specifies the database server name and,
-T specifies to use Windows Authentication.

The output generated by BCP is slightly different than of SQLCMD, output generated by BCP is as follows:

1,Chief Executive Officer,M,1963-03-02
2,Vice President of Engineering,F,1965-09-01
3,Engineering Manager,M,1968-12-13
4,Senior Tool Designer,M,1969-01-23
5,Design Engineer,F,1946-10-29

You can see that there is no padding while exporting using BCP by default, same can be achieved by using -W switch with SQLCMD.

 

If you need to implement any "complex" formatting, such as using delimiter for starting and end of row or change data format you can do this inside the stored procedure created for exporting data.

USE AdventureWorks2008R2

 

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 '#' + CAST(BusinessEntityID AS NVARCHAR(MAX)) + '#' +

            CAST(JobTitle AS NVARCHAR(MAX)) + '#' +

            CAST(Gender AS NVARCHAR(MAX)) + '#' +

            CONVERT(NVARCHAR(20), BirthDate, 107) + '#'

FROM        HumanResources.Employee

Here I have returned result as a single column with specifying ‘#’ as a delimiter and to denote start/end of a row. So all rows returned by stored procedure will be pre-formatted and -s or -t switches of SQLCMD/BCP will have no effect.

#1#Chief Executive Officer#M#Mar 02, 1963#
#2#Vice President of Engineering#F#Sep 01, 1965#
#3#Engineering Manager#M#Dec 13, 1968#
#4#Senior Tool Designer#M#Jan 23, 1969#
#5#Design Engineer#F#Oct 29, 1946#

 

To see all switches available for BCP/SQLCMD refer BOL links or execute them with -? at command prompt.

 

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