Insert custom header row in BCP output

Long story short we have a database here that manages some employee data such as email, first name, last name, etc. Our company bought into this SAP Based expense report system :| that needs an export of our employee based data in a very strange format. Without getting into too many details the export of this data needs a total of 137 columns, with many of these columns having an empty value.

Simple I put together a query that basically pulled this information from our database, and I set some constants to what was needed. It's not relevant what this query is in this question, its simply a SELECT statement that pulls some data.

I then needed to export this out on a daily basis with a specific file name and with a pipe delimited format, something to this effect:

--employee export
DECLARE @FileName varchar(500)
SET @FileName = (SELECT '\\someFileServer\Public\someFolder\employee_p06010603ace_305_202105_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, getdate(), 112), 126), '-', ''), 'T', ''), ':', '') + '.txt')
DECLARE @sql varchar(8000)
DECLARE @header varchar(8000)
SET @sql= 'bcp "exec [MyDBServer].[MyDbName].dbo.ConcurEmployeeExport" queryout ' + @FileName + ' -c -T -t "|"'
Exec master..xp_cmdshell @sql

Perfect, I get rows like this:

305|Jon| |Doe|10217487|10217487@t| 
305|Steve| |Smith|10217522|10217522@t|

With about 130 more columns, didn't want to display all of this since it is irrelevant. I thought I was done until the implementation coordinator said yes looks good except the first row needs to start with this strange row with some values.

Meaning the resultant query that I have should have one additional row with some values in it..simple I thought I would union what they wanted..and so I did.

Except the UNION ALL I had to put in some additional empty values to match the number of columns in my query. The issue is when I run the export I end up with a bunch of empty columns that are pipe delimited, I end up wi

One option you might consider is separating the two. Create one file with your export and all fields. The other file just has your header. The last step would be to combine the two with something like this:

REM create the header file
ECHO 100|0|SSO|UPDATE|EN|N|N| >"MyExport.txt.header"
REM append the bcp export to the header file
TYPE "MyExport.txt">>"MyExport.txt.header"
REM rename the header file back to the export file name
MOVE /y "MyExport.txt.header" "MyExport.txt"

You mentioned the export was done daily. Is there an SQL job schedule to do this? This could be a step added to the job using the Operating system (CmdExec) type. It doesn't have to be an "external" process if I understand you correctly. All of the code can be entered into the job step.

If you're not familiar with it, the other thing you can look into is SQLCMD. Bottom line is I believe you are correct: What you want probably can't be done using bcp directly in one step.

StackExchange Database Administrators Q#125885, answer score: 4