export all table to excel with header using bcp

• Luka Gros

  SSC Rookie

  Points: 42

  More actions

  November 19, 2012 at 7:33 am

  #1560369

  Lowell (7/11/2011)

  ---

  if you have to do this via bcp and not SSIS, then there are two ways that i can think of.

  one exports a global temp table, and the other uses the command line COPY command to append two files together.

  --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

  I had the same problem. I didn't combine two files, I just added UNION ALL and it works perfectly 🙂

  EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT ''CITYTBLKEY'',''CITYNAME'',''COUNTYTBLKEY'',''COUNTYNAME'',''COUNTYFIPS'',''STATETBLKEY'',''STATECODE'',''CITYDESCRIP''

  union all

  SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM VW_CITYCOUNTYSTATE

  " queryout MyFileNameT1.txt -c -T'