Scroll to top
  • Contact Us - (480) 750-9756
  • contact@hookesconsult.com

Implementing the OUTPUT Clause in SQL Server


admin - August 13, 2019 - 0 comments

At work, I often find myself exporting data in .csv format for our clients. I am talking about thousands of data, to the point that I never really noticed this issue.

I am talking about data that contain special characters like a comma for example.

So once in a while, I’d encounter names with commas in them. Names with suffixes like “Smith, Jr”, “Donald, Jr”, or “Elizabeth, III” and so on.

When exporting to a comma separated value .csv file using SQL Server Integration Services (SSIS), the flat file destination connection manager assumes that this comma is the end of the data and therefore “spills” the rest of the data into the next column. The problem with this is that it disrupts the arrangement of the output data thereby causing inconsistent and even incorrect output.

In the following example, I will be showing how to correct this issue using a text qualifier:

Before I continue, I will create my sample data below:

 

CREATE TABLE dbo.Employee
 (
  EmpID  INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  MiddleName VARCHAR(20),
  BirthDate DATE,
  HireDate DATE,
  Salary  MONEY,
  EndDate  DATE
 )
 
INSERT INTO dbo.Employee (EmpID, FirstName, LastName, MiddleName, BirthDate, 
      HireDate, Salary, EndDate)
VALUES
(1, 'John', 'Brown, Jr.','K', '01-01-1960', '10-01-1985', 85000.00, GETDATE()),
(2, 'Pete', 'Dawn, III', NULL,'12-03-1965', '12-01-2000', 60000.00, NULL),
(3, 'Amanda', 'Lopez', NULL,'06-01-1980', '12-01-2005', 45000.00, NULL),
(4, 'Adam', 'Smith, Sr.', NULL, '02-26-1971', '04-23-2001', 70000.00, GETDATE()),
(5, 'Taylor', 'Ramsey, Jr', NULL, '03-01-1984', '11-11-2004', 60000.00, NULL)


SELECT * FROM dbo.Employee
My result is shown below:

Post a Comment

Your email address will not be published. Required fields are marked *