Header/Trailer Record

Transact-SQL
1

Hey, everyone.

I've got a script here to put my table into a flat file format. My problem is I need to add a header record and a trailer record. Here's a condensed version of my script:

select

+LEFT(FNAME + SPACE(35),35)
+LEFT(MNAME + SPACE(35),35)
+LEFT(LNAME + SPACE(35),35)
+LEFT(DOB + SPACE(8),8)
+INSTCODE
 
from 
(select 
		FIRSTNAME AS FNAME,
		MIDDLENAME AS MNAME,
		LASTNAME AS LNAME,
		DOB AS DOB,
		INSTCODE='002'

		from Manual.dbo.Met_Need GE
				) sub
 
  order by 1

That gives me a flat-file format of the records in my table. What I'm needing is to add a row at the beginning and a row at the end.

The header row needs to say:

000                         GE STUBCAR SUBMITTAL20150731S                                                                            00100300

And the trailing row would be similar, but slightly different.

How can I make this work?

2 
    SELECT header = '000                         GE STUBCAR SUBMITTAL20150731S'
    
    UNION ALL
    
    -- your existing query here
    SELECT  .......

  UNION ALL

SELECT trailer = ' . . .  . '
1 Like
3

If you have a problem with the ORDER BY then my solution would be

SELECT MyData
FROM
(
SELECT 1 AS [MySequence]
       , '000                         GE STUBCAR SUBMITTAL20150731S ' AS MyData
UNION ALL
SELECT 2
       , LEFT(FNAME + SPACE(35),35)
       + LEFT(MNAME + SPACE(35),35)
...
UNION ALL
SELECT 3
       , 'EndMarkerData'
) AS X
ORDER BY MySequence, MyData

(It then, also, doesn't matter what sequence you arrange the Header/Footer/Body in)

1 Like
4

Worked like a charm! I knew it was simple, I was just drawing a blank. THANK YOU!