t-sql output file in need of headers and trailers

Question

Post reply

t-sql output file in need of headers and trailers

A-214252

Mr or Mrs. 500

Points: 503
More actions
July 13, 2005 at 9:36 am

#88081

I have some T-SQL output scripts (they work very nicely, so no I won't go DTS route!) that are in need of header/trailer data (like date, qty etc..) my solutions are getting progressively worse (no I won't do batch files!)...thanks all.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

July 13, 2005 at 9:39 am

#573973

What do you want to do exactly (exemple??)?

Ray M SSC-Insane Points: 21093 More actions · Answer 2

A, A

What do you mean you need headers and footers?

Is your header the column names? Is it some other derived value.

U can try the union statement. Use the NULLS TO make the number of columns the same as your main select.

Select 'This', 'Is', 'my', 'Header', NULL, NULL, NULL, NULL, NULL

Union

{Select Statement}

Union

Select 'This', 'is', 'My', 'Footer information', NULL, NULL, NULL, NULL, NULL.

But because of the amount of information you have provided I am guessing

A-214252 Mr or Mrs. 500 Points: 503 More actions · Answer 3

Parles-tu Francais?

I have a select statement, that generates a flat file, and sends it to a FTP site. These files need batch records appended to them (header/footer) that have nothing to do with the actual data.

Ray M SSC-Insane Points: 21093 More actions · Answer 4

Okay, Gathered that from original post. Can you provide us with an example?

Did you try the Union Method I posted earlier?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Yes I speak french. Also if the method Ray posted doesn't work we'll need some sample data you need to generate.

sara karasik SSCommitted Points: 1573 More actions · Answer 6

Is this the format that you want your information?

header line of column names

data

footer line of column summary data?

Try this:

select 1, 'FieldOneName', 'FieldNameTwo', 'FieldNameThree'

union

select 2, convert(varchar(), FieldOne), , convert(varchar(), FieldTwo), , convert(varchar(), FieldThree) from WhatEverTableYouAreUsing

union

select 3, convert(varchar(), sum(FieldOne)), , convert(varchar(), count(FieldTwo)), , convert(varchar(), max(FieldThree)) from WhatEverTableYouAreUsing

NOTE! You need to convert everything to varchar since the column names are varchar. Each select should start with a number, so that the data will be returned in the order that you want it, in other words, 1-header, 2-data, 3- footer

A-214252 Mr or Mrs. 500 Points: 503 More actions · Answer 7

Interesting, I never knew you could append a ranking next to the select command. Works very well. My header/footers are not column names or fields, but extraneous(dates, batch numbers etc..). I think I'll combine, Ray M's post with your ranking schemes. This way I can fool with header/footer without messing with the data core. Thanks Again.

A