bcp append a line at the end of file

Question

I am trying to export a .txt file with bcp:

EXEC xp_CMDSHELL 'BCP "SELECT v.ItemId
  , v.InventLocationId
  , CAST(v.AvailPhysical AS int)
FROM D_R1.dbo.vwStockOnHand AS v 
JOIN D_R1.DBO.vwProducts AS m 
ON v.ItemId = m.ITEMID 
 WHERE  NOT EXISTS 
     (SELECT 1 
      FROM D_R1.DBO.ExportExcludedFamilies AS magExport 
      WHERE magExport.REFRECID = m.FamilyRecId) 
      AND  AVAILPHYSICAL > 0 AND v.PICKFROMZ = 1 
      ORDER BY  v.InventLocationId, v.ITEMID"
      queryout "C:\temp\1.txt" -c -t"|" -T -S D_R1'

All good, but i would need a last line in the file like "END OF FILE" and i am not able to figure it out ..

Can someone give me a hint on this?

Answer 1

1) First solution

SELECT CONVERT(VARCHAR(11), v.ItemId) AS ItemId
  , v.InventLocationId
  , CAST(v.AvailPhysical AS int)
  , 1 AS Priority
FROM ...

UNION ALL

SELECT 'End of export file', NULL, NULL, 2 AS Priority

ORDER BY Priority, InventLocationId, ItemID

or

SELECT... ;
SELECT 'End of report' ;

or

SELECT... ;
PRINT 'End of report' ;

2) Second solution

 bcp "the same SQL query", ...
 echo End of export

echo command