asked on

Inserting a Header and Footer row using SQL

Hi, below is my SQL which I am using to export to a | demited text file using SSIS. However I now
need to add a Header and Footer to the file preferably in my below SQL itself, if possible ?

Header (both the below values are fixed and never change)
HD,1.0

MY existing SQL for the export file

SELECT [Record Type],[Provider CIS No]
FROM (
SELECT DISTINCT 'CI' AS 'Record Type'

             ,CUSTNO AS 'Provider CIS No'
             ,c.cd
FROM customer c
UNION
SELECT DISTINCT 'BD'
             ,CUSTNO AS 'Provider CIS No'
             ,c.cd
FROM customer c
WHERE YEAR(c.cd) = YEAR(getdate()) AND MONTH(c.cd) = MONTH(getdate())
) t
ORDER BY cd,[Provider CIS No]

Footer
FT,(count of records from my SQL above)

can anyone please give me that SQL with the header and footer ?

Many Thanks

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gvamsimba

ASKER

Hi Jim, but as you can see in my question, my body SQL has got an ORDER BY. Where is that in your sql ?

Jim Horn

You can add that to the ORDER BY of the main query as the second column onwards and it'll still work.

ORDER BY sort_order, cd, [Provider CIS No]

Open in new window

gvamsimba

ASKER

Hi Jim, I am bit confused. can you please add that header and footer sql to my existing sql to see how exactly it looks ?

Many Thanks

gvamsimba

ASKER

also I cannot see your footer counting the number of records of the main query

gvamsimba

ASKER

Hi Experts, can anyone please help on this ?

Many Thanks

gvamsimba

ASKER

Hi, can anyone please help with this ?

Thank you.

gvamsimba

ASKER

good