--BCP to get headers
--Version 1: global temp table
--not i'm making sure my headcers are large enough for my data i insert later....
--otherwise CITYNAME wouldbe varchar(8), and I need maybe 100
SELECT 
	identity(int,1,1) AS BCPORDERID,
	CONVERT(varchar(100),'CITYTBLKEY') AS CITYTBLKEY,
	CONVERT(varchar(100),'CITYNAME') AS CITYNAME,
	CONVERT(varchar(100),'COUNTYTBLKEY') AS COUNTYTBLKEY,
	CONVERT(varchar(100),'COUNTYNAME') AS COUNTYNAME,
	CONVERT(varchar(100),'COUNTYFIPS') AS COUNTYFIPS,
	CONVERT(varchar(100),'STATETBLKEY') AS STATETBLKEY,
	CONVERT(varchar(100),'STATECODE') AS STATECODE,
	CONVERT(varchar(100),'CITYDESCRIP') AS CITYDESCRIP
INTO ##BCP_Results
	
INSERT INTO ##BCP_Results(CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP)
SELECT  
    CITYTBLKEY,
    CITYNAME,
    COUNTYTBLKEY,
    COUNTYNAME,
    COUNTYFIPS,
    STATETBLKEY,
    STATECODE,
    CITYDESCRIP
FROM VW_CITYCOUNTYSTATE

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM ##BCP_Results ORDER BY BCPORDERID" queryout MyFileName.txt -c -T'

--drop table ##BCP_Results

--Version TWO:
--export two files, then combine them together.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT ''CITYTBLKEY'',''CITYNAME'',''COUNTYTBLKEY'',''COUNTYNAME'',''COUNTYFIPS'',''STATETBLKEY'',''STATECODE'',''CITYDESCRIP''" queryout MyFileNameT1.txt -c -T'
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM VW_CITYCOUNTYSTATE" queryout MyFileNameT2.txt -c -T'
set @sql ='copy c:\MyFileNameT.txt + c:\MyFileNameT2.txt c:\MyFileName.txt'
EXEC master..xp_cmdshell  @sql