Site Sections
Posts
83
Comments
600
Trackbacks
40
<< OSQL Doesn't work like Query Analyzer ( Or Query Analyser for that matter...) | Home | T-SQL HTML? Parser >>
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

posted on Thursday, June 29, 2006 1:03 PM Print
Comments
# re: How do I create a file with a header and trailer?
Spyder Ryder
7/5/2006 4:31 AM
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.
# re: How do I create a file with a header and trailer?
Brett
7/5/2006 11:25 AM
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.
Comments have been closed on this topic.
Article Categories
Archives
Post Categories
Image Galleries
Misc
SQL Server