How to add multiple select statement one bcp command in SQL Server

Question

I have a bcp command to create a file with columns and headers. I need to add timestamp at the top of the file and Ctrl C at the end. How do I achieve this?

This works fine with queries having union all.

DECLARE @Query VARCHAR(5000) ='select ''Col1'', ''Col2'', ''Col3'' union all select col1, col2, col3 from TestTable ' 
DECLARE @cmd1 VARCHAR(5000)='bcp "' + @Query +'" queryout "' +  @File + '" -c -T' 

I tried to put them in multiple queries and concatenate them to one query

This is what I tried

DECLARE @Query1 VARCHAR(5000)='Select Getdate();'
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @Query2 VARCHAR(5000) ='select ''Col1'', ''Col2'', ''Col3'' union all select col1, col2, col3 from TestTable '
DECLARE @Query VARCHAR(5000)=@Query1 + @NewLineChar + @Query2 
DECLARE @cmd1 VARCHAR(5000)='bcp "' + @Query +'" queryout "' +  @File + '" -c -T' 

The output file should be like this

12/14/18 17:10

Col1 Col2 Col3
1      2   3  
4      5   6
^C

Answer 1

Simple way will be create a view with header and data (unionall) and query the view in your BCP