x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

How do I create a file with a header and trailer?

...was recently asked at SQLTeam

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68461

This is what I would do

USE Northwind
GO

CREATE VIEW EXPORT_ORDERS
AS
SELECT 1 AS ROW_ORDER,
  'HEADER '
 + CONVERT(char(25),GetDate()) AS Data_Line
UNION ALL
SELECT 2 AS ROW_ORDER,
   COALESCE(CONVERT(char(15),OrderID),'')
 + COALESCE(CustomerID,'')
 + COALESCE(CONVERT(char(15),EmployeeID),'')
 + COALESCE(CONVERT(char(25),OrderDate),'')
 + COALESCE(CONVERT(char(25),RequiredDate),'')
 + COALESCE(CONVERT(char(25),ShippedDate),'')
 + COALESCE(CONVERT(char(15),ShipVia),'')
 + COALESCE(CONVERT(char(15),Freight),'')
 + COALESCE(CONVERT(char(80),ShipName),'')
 + COALESCE(CONVERT(char(120),ShipAddress),'')
 + COALESCE(CONVERT(char(30),ShipCity),'')
 + COALESCE(CONVERT(char(30),ShipRegion),'')
 + COALESCE(CONVERT(char(20),ShipPostalCode),'')
 + COALESCE(CONVERT(char(30),ShipCountry),'') AS Data_Line
  FROM Orders
UNION ALL
SELECT 3 AS ROW_ORDER,
  'TRAILER '
 + CONVERT(char(25),GetDate())
 + CONVERT(char(15),COUNT(*)) AS Data_Line
  FROM Orders
GO

SELECT Data_Line FROM EXPORT_ORDERS ORDER BY ROW_ORDER

EXEC master..xp_cmdshell 'bcp Northwind.dbo.EXPORT_ORDERS out C:\Orders.txt -S<servername> -c -T'
GO

DROP VIEW EXPORT_ORDERS
GO

Print | posted on Thursday, June 29, 2006 1:03 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: How do I create a file with a header and trailer?

It's funny how solving one problem triggers another. I would've used ISNULL rather than COALESC but it's given me a great idea for solving another issue where we report quantities or values in the same column and have previously used all sorts of jiggery pokery to get it out. So thanks for sharing that gem with us.
7/5/2006 4:31 AM | Spyder Ryder
Gravatar

# re: How do I create a file with a header and trailer?

Thanks for looking.

I use COALESCE because it is ANSI and ISNULL isn't. I'm sure it's a matter of personal preference, but I have to shift gears between many different platforms, so the less I have to remember, the better.
7/5/2006 11:25 AM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET