Adding Header and Trailer

Question

Adding Header and Trailer

sqlenthu_11

SSC Eights!

Points: 800
More actions
May 13, 2010 at 8:14 am

#386927

Need an urgent help to add Header and Trailer in the Sql extract using T-Sql. The format should be :
1)Header: 999999999+Filename+CurrentDate
2)Detailed Records
3)Trailer :99999999+No.of Records in the extract.
For ex.
0000000000INMSFTACBS20090413 <--Header Information
ATM Automated Teller Machine
ACQUIRING Merchant Payments
PREPAID Closed Loop Gift Cards
888888888000000003 <-Trailer:888888+No.of records in extract
Thanks,
Nitin

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

What you are describing is usually a bad idea. You're almost always better off doing separate returns and handling the formatting on the application side. However, if you really need to follow this path, this is one possible method:

declare @t_table table --creating table to have some data to work with

(

Column1varchar(50)

)

insert into @t_table (Column1)

select 'cheese' union

select 'cabbage' union

select 'beer'

select Detail as Results --this is the actual query

from

(

select 0 as RowID,

'this is my header row' as Detail

union

select ROW_NUMBER() OVER (ORDER BY Column1) as RowID,

Column1 as Detail

from @t_table

union

select COUNT(Column1) + 1 as RowID,

'this is my trailer ' + cast(COUNT(Column1) as varchar) + ' records' as Detail

from @t_table

) SQ

order by RowID

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

nitin_456 (5/13/2010)
Need an urgent help to add Header and Trailer in the Sql extract using T-Sql. The format should be :
1)Header: 999999999+Filename+CurrentDate
2)Detailed Records
3)Trailer :99999999+No.of Records in the extract.
For ex.
0000000000INMSFTACBS20090413 <--Header Information
ATM Automated Teller Machine
ACQUIRING Merchant Payments
PREPAID Closed Loop Gift Cards
888888888000000003 <-Trailer:888888+No.of records in extract
Thanks,
Nitin

Lookup "UNION ALL" in Books Online

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Be advised that UNION ALL will only help you if the header/trailer rows have identical layouts. The whole header/trailer idea is totally alien to the way SQL works. Append a header and trailer from your calling application.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 4

sqlenthu_11

SSC Eights!

Points: 800

May 14, 2010 at 1:28 pm

#1167752

Thanks a lot for replying!!!!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

The Dixie Flatline (5/13/2010)
Be advised that UNION ALL will only help you if the header/trailer rows have identical layouts. The whole header/trailer idea is totally alien to the way SQL works. Append a header and trailer from your calling application.

I should have also added that you need to "flatten" each line so that header, body, and footer are all just one big column of text.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 6

I've had to do this a number of times from within SQL Server. The neatest way I have found is to create a T-SQL object (procedure or function) to fetch the detail records in an efficient way, and call that from a SQLCLR routine that handles streaming the data (plus header and footer) from the database to an external file.

T-SQL is very good at manipulating data stored in databases.

The .NET framework makes it easy to write files.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Paul White NZ (5/15/2010)
I've had to do this a number of times from within SQL Server. The neatest way I have found is to create a T-SQL object (procedure or function) to fetch the detail records in an efficient way, and call that from a SQLCLR routine that handles streaming the data (plus header and footer) from the database to an external file.
T-SQL is very good at manipulating data stored in databases.
The .NET framework makes it easy to write files.
Paul

Agreed.

As a sidebar, the way I used to do this was to either BCP out the 3 pieces and put them back together with the DOS COPY command or use OSQL if the DBA doesn't want to take the time to set up a command shell proxy. Seems like that simple methodology has gone out of style but it still works well. Heh... I suppose SSIS could be made to do the job, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 8

Jeff Moden (5/15/2010)
As a sidebar, the way I used to do this was to either BCP out the 3 pieces and put them back together with the DOS COPY command or use OSQL if the DBA doesn't want to take the time to set up a command shell proxy. Seems like that simple methodology has gone out of style but it still works well. Heh... I suppose SSIS could be made to do the job, as well.

Yes, there's nothing wrong with a bit of batch file magic!

SSIS is certainly an option, though you'd probably end up writing some .NET code in a Script component to construct the header and footer anyway.

In the particular case I had in mind, the output was required in XML (there was more than a header and footer to add too!) so the XML capabilities of .NET were an added bonus.

I fully expect someone to post a PowerShell script at some stage :hehe:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

Absolutely agreed on all points. There's many ways to skin this particular cat.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)