September 17, 2009 at 9:58 am
Hi ,
I am new to TSql and need to add dummy headers to my sql result before i export it to a csv format.
i can have a header which can be a noddy as '1' or 'header' as the macro that need to read my result start reading data from the 3rd row
Many thanks,
Christian
September 18, 2009 at 9:00 am
First, a disclaimer.
This is most likely not the best method for this. There may be an exporting service like SSIS or one of those that can handle this much more eloquently. Unfortunately, I don't deal with those and can't tell you which you can use.
*A* way to do this using TSQL would be:
Example Data set:
Date User Sales Earnings
9/1/09 Mike 15 500.00
9/2/09 Sally 12 400.00
9/5/09 Greg 52 4000.00
Say this query generates the dataset normally:
SELECT Date, User, Sales, Earnings
FROM MagicalReportReadyTable
This is how you could do it to include column headers in the result set:
SELECT CAST('Date' as varchar(25)) Date,
CAST('User' as varchar(50)) User,
CAST('Sales' as varchar(6)) Sales,
CAST('Earnings' as varchar(12)) Earnings
UNION ALL
SELECT CAST(Date as varchar(25)),
CAST(User as varchar(50)),
CAST(Sales as varchar(6)),
CAST(Earnings as varchar(12))
FROM MagicalReportReadyTable
You need to cast the values because your column headers are all char types and your data is likely not. I'm casting the initial ones because UNION ALL takes the datatypes from the first resultset, and unless I cast them, you may not have enough character width with the column headings. The field Aliases are extraneous given what you're using this for, but I like using them anyways. Don't mind my rampant usage of keywords as column names, this was just a quick example 🙂
September 24, 2009 at 8:11 pm
There are several ways to do this but it really depends on your preferences.
You can use the union method above.
If you plan on executing this query from SSMS everytime; you could set the query options to place a header on your result set. (query -> query options; select grid on left pane and check "include column headers when copying or saving the result". Then when you get the result set you can right click and save.
You could also create an SSIS package and set the file properties to include the header. This gives you an option of running this as a job, then it's a set it up once and schedule it to run.
Another option is to embed the query in excel, then you refresh the worksheet and excel automatically puts the headers on. Then do a save as *.csv. This allows you the option of handing it off to end user to refresh at their leisure.
There's probably many more methods, but it really comes down to where you do most of your work, what you are most comfortable with, and how many of these you will be doing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply